11 Replies Latest reply: Nov 6, 2018 5:15 AM by Ekaterina Ponkratova RSS

    Access flags from canonical date table in set analysis

    Ekaterina Ponkratova

      I I feel like I am taking advantage of the knowledge of people here. Sorry, if I abuse it. With me positing here, it doesn't mean that I am not trying myself but I figured that when I stuck absolutely, I should ask someone who has more expertise. It hurts my ego, but helps to get results faster.

       

      Yesterday I was working with canonical dates - I have not closed the discussion yet at https://community.qlik.com/message/1566945#1566945?sr=tcontent because I still need to get to the calculation that uses the results of that concatenation.

       

      Let's say that I need to calculate the sum of the berthing time for all vessel visits. I added the vessel visit (from both, the move and vessel tables) to the Bridge table (see the code below). So, how can I access the vessel visits ONLY those that have the tag 'vessel'? In the LinkTable, one row represents, roughly speaking, one container. Which means that one vessel visit could have several rows in the LinkTable.

      The last version that I have been working on is:

      aggr(sum((TimeStamp - VSL_BERTH_D)*24),visit{<type={'vessel'}>})

      sum(if(type='vessel',aggr(((TimeStamp - VSL_BERTH_D)*24),distinct visit)))

       

      but of course it doesn't work. I think it's because I am accessing the rows that I am interested in incorrectly. I have googled but have not found how to access the flags from the canonical date table or there is no difference from accessing flags in the ordinary set analysis?

       

      LinkTable:

      LOAD

      m_row_id,

          visit,

          container,

          Timestamp([TET],'MM/DD/YYYY hh:mm:ss tt') as TimeStamp,  

        'move' as type,

          If(Time(Frac([TET])) >= ('06:00:00 AM') and Time(Frac([TET]))< ('02:00:00 PM'), 'Morning',

          If(Time(Frac([TET])) >= ('02:00:00 PM') and Time(Frac([TET])) < ('10:00:00 PM'), 'Afternoon',

          If(Time(Frac([TET])) >= ('10:00:00 PM') and Time(Frac([TET])) < ('11:59:59 PM'), 'Night',

          If(Time(Frac([TET])) >= ('12:00:00 AM') and Time(Frac([TET])) < ('06:00:00 AM'), 'Night' ,0   )))) as shift   

      Resident Moves

      ;

      Concatenate(LinkTable)

      LOAD

      v_row_id,

          visit,

          container,

          Timestamp(`EST_DPTR_D`,'MM/DD/YYYY hh:mm:ss tt') as TimeStamp,     

      'vessel'  as type,

          If(Time(Frac(`EST_DPTR_D`)) >= ('06:00:00 AM') and Time(Frac(`EST_DPTR_D`))< ('02:00:00 PM'), 'Morning',

          If(Time(Frac(`EST_DPTR_D`)) >= ('02:00:00 PM') and Time(Frac(`EST_DPTR_D`)) < ('10:00:00 PM'), 'Afternoon',

          If(Time(Frac(`EST_DPTR_D`)) >= ('10:00:00 PM') and Time(Frac(`EST_DPTR_D`)) < ('11:59:59 PM'), 'Night',

          If(Time(Frac(`EST_DPTR_D`)) >= ('12:00:00 AM') and Time(Frac(`EST_DPTR_D`)) < ('06:00:00 AM'), 'Night',0)))) as shift       

      Resident Vessel

      ;

      Concatenate(LinkTable)

      LOAD

      g_row_id,

          container,

      Timestamp([TRACTOR_DEPART_DATE],'MM/DD/YYYY hh:mm:ss tt') as TimeStamp, 

      'gate'  as type,

          If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('06:00:00 AM') and Time(Frac([TRACTOR_DEPART_DATE]))< ('02:00:00 PM'), 'Morning',

          If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('02:00:00 PM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('10:00:00 PM'), 'Afternoon',

          If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('10:00:00 PM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('11:59:59 PM'), 'Night',

          If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('12:00:00 AM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('06:00:00 AM'), 'Night',0)))) as shift       

      Resident Gate

      ;

      DROP FIELD container, visit  FROM Vessel, Moves, Gate;

        • Re: Access flags from canonical date table in set analysis
          Ekaterina Ponkratova

          With Only({$<type={'vessel'}>}visit), I get only the vessels that are tagged 'vessel' in the LinkTable. Now I just need to sum the difference of times.

           

          I feel I am very close with sum(aggr(((Only({$<type={'vessel'}>}TimeStamp)-VSL_BERTH_D)*24),Only({$<type={'vessel'}>}visit))).

           

          And I have it!

          sum({$}aggr((((Only({$<type={'vessel'}>}TimeStamp)-VSL_BERTH_D)*24)), visit))

            • Re: Access flags from canonical date table in set analysis
              Robert Hutchings

              Hi

              does

               

              sum({$<type={'vessel'}>}

              TimeStamp-VSL_BERTH_D  *24)

               

              work

               

              I try to avoid aggr as its slows down the calculation speed (and ensure it gives a right total)

               

              Although I don't know where VSL_BERTH_D comes from

               

              "So, how can I access the vessel visits ONLY those that have the tag 'vessel'?"

               

              Exactly as you have done it. You could also use if(Type = 'vessel' etc but set analysis is recommended = quicker

               

              I concatenate tables when required and use set analysis to only access the table I want. Never if now

                • Re: Access flags from canonical date table in set analysis
                  Ekaterina Ponkratova

                  Hi Robert,

                   

                  Nope, it shows - as a result.

                   

                  It's so tiresome to work with the formula with my knowledge. For example, I got the above formula working but then, I am using it in the graph to show the trend and everything crumbles. Banging my head against the wall.

                    • Re: Access flags from canonical date table in set analysis
                      Robert Hutchings

                      can you load the full script and data model. I will look at it when I get back

                        • Re: Access flags from canonical date table in set analysis
                          Robert Hutchings

                          btw did u have bracket around the time.in my expression.  I forgot them

                            • Re: Access flags from canonical date table in set analysis
                              Ekaterina Ponkratova

                              Brackets? What place?

                               

                              The current version is:

                              /************************************************

                              Generating the auto-calendar as Qlik doesn't recognize the datetime from SQL correctly

                              ************************************************/


                              Calendar:

                                DECLARE FIELD DEFINITION Tagged ('$date')

                              FIELDS

                                Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

                                Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

                                Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

                                Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

                                Month($1) AS [Month] Tagged ('$month', '$cyclic'),

                                Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

                                Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

                                Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

                                Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

                                Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

                                If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

                                Year(Today())-Year($1) AS [YearsAgo] ,

                                If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

                                4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

                                Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

                                If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

                                12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

                                Month(Today())-Month($1) AS [MonthRelNo] ,

                                If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

                                (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

                                Week(Today())-Week($1) AS [WeekRelNo]

                                ,year($1) as year

                                ,month($1) as month

                                ,day($1) as day

                                ,weekday($1) as weekday

                                ,hour($1) as hour

                                ,minute($1) as minute

                                ,week($1) as week 

                                 

                                ;

                               

                              /************************************************

                              Loading data

                              ************************************************/


                              LIB CONNECT TO [qlik_32];


                              Vessel:

                              LOAD

                              RowNo() as v_row_id ,

                              [idvessl],

                              [VISIT_VSL_LENGTH],

                              [STOW_ACTIVITY_CODE],

                              [VSC_BERTH],

                                  `VESSEL_VISIT_C` as visit,

                                  `DEPARTURE_LAST_LINE_TM`,

                                  `EST_ARV_D`,

                                  `EST_DPTR_D` ,

                                  `VSL_BERTH_D`,

                                  `ACTL_ARV_D`,

                                  `WORK_START_TM`,

                                  `WORK_END_TM`,

                                  `VISIT_VSL_NAME_AN`,

                                  `CNTR_LADEN_STATUS`,

                                  `SHIPPING_STATUS_CODE`,

                                  `CNTR_NUM` as container,

                                  `CNTR_OPER_CODE`,

                                  `CNTR_MOST_HAZARDOUS_IMCO_CODE`,

                                  `CNTR_IMCO_UNDG_CODE`,

                                  `CNTR_ARRIVE_TM`,

                                  `CNTR_DEPART_TM`,

                                  `CNTR_YARD_AGE_IN_2_DECM`,

                                  `CRANE_C`,

                                  `len` as [Len_vessel],

                              [VSL_LINER_VOYAGE],

                              [TEU],

                              [MOIS];

                              SQL SELECT `idvessl`,

                              `VISIT_VSL_LENGTH`,

                              `STOW_ACTIVITY_CODE`,

                              `VSC_BERTH`,

                              `VESSEL_VISIT_C`,

                              `DEPARTURE_LAST_LINE_TM`,

                              `EST_ARV_D`,

                              `EST_DPTR_D`,

                              `VSL_BERTH_D`,

                              `ACTL_ARV_D`,

                              `WORK_START_TM`,

                              `WORK_END_TM`,

                              `VISIT_VSL_NAME_AN`,

                              `CNTR_LADEN_STATUS`,

                              `SHIPPING_STATUS_CODE`,

                              `CNTR_NUM`,

                              `CNTR_OPER_CODE`,

                              `CNTR_MOST_HAZARDOUS_IMCO_CODE`,

                              `CNTR_IMCO_UNDG_CODE`,

                              `CNTR_ARRIVE_TM`,

                              `CNTR_DEPART_TM`,

                              `CNTR_YARD_AGE_IN_2_DECM`,

                              `CRANE_C`,

                              `len`,

                              `VSL_LINER_VOYAGE`,

                              `TEU`,

                              `MOIS`

                              FROM `cliksense`.`vessels`;   


                              Moves:

                              LOAD

                              RowNo() as m_row_id,

                              [idmove],

                              [MOV_KIND] AS [MovKind_move],

                              [FETCH],

                              [WSTH],

                              [FETCHOP],

                              [CARRY],

                              [CARRYOP],

                              [PUT],

                              [PUTOP],

                              [COMP_MOVE],

                              [CONTAINER] as container,

                              [MOVE_TO],

                              [MOVE_FROM],

                              [CONTAINER_TYPE_C],

                              [IN_VESSEL_VISIT_C],

                              [OUT_VESSEL_VISIT_C],

                              [OPER_C] AS [Line_move],

                              [CATEGORY] AS [Category_move],

                              [EQP_TYP_C],

                              [CNTR_LOAD_STATUS_CODE],

                              [ACTV_REEF_S],

                              [TEU] AS [TEU_move],

                              [LASTUPDATE],

                              [VESSEL_VISIT_C] as visit,

                              [CRANE],

                              [TET];

                              SQL SELECT `idmove`,

                              `MOV_KIND`,

                              `FETCH`,

                              `WSTH`,

                              `FETCHOP`,

                              `CARRY`,

                              `CARRYOP`,

                              `PUT`,

                              `PUTOP`,

                              `COMP_MOVE`,

                              `CONTAINER`,

                              `MOVE_TO`,

                              `MOVE_FROM`,

                              `CONTAINER_TYPE_C`,

                              `IN_VESSEL_VISIT_C`,

                              `OUT_VESSEL_VISIT_C`,

                              `OPER_C`,

                              `CATEGORY`,

                              `EQP_TYP_C`,

                              `CNTR_LOAD_STATUS_CODE`,

                              `ACTV_REEF_S`,

                              `TEU`,

                              `LASTUPDATE`,

                              `VESSEL_VISIT_C`,

                              `CRANE`,

                              `TET`

                              FROM `cliksense`.`moves`;


                              Gate:

                              LOAD

                              RowNo() as g_row_id,

                              [idgate],

                              [GATE_VISIT_ID],

                              [TK] as TK_gate,

                              [CNTR] as container,

                              [OPR] as Line_gate,

                              [CNTR_TYPE],

                              [LEN] as Leng_Gate,

                              [TRACTOR_ARRIVE_DATE],

                              [IN_LANE],

                              [TRACTOR_DEPART_DATE] ,       

                              [OUT_LANE],

                              [USERIN],

                              [USEROUT],

                              [GATE_MISSION_CODE],

                              [status],

                              [TERMINAL_TURN_TIME_NM];

                              SQL SELECT `idgate`,

                              `GATE_VISIT_ID`,

                              `TK`,

                              `CNTR`,

                              `OPR`,

                              `CNTR_TYPE`,

                              `LEN`,

                              `TRACTOR_ARRIVE_DATE`,

                              `IN_LANE`,

                              `TRACTOR_DEPART_DATE`,

                              `OUT_LANE`,

                              `USERIN`,

                              `USEROUT`,

                              `GATE_MISSION_CODE`,

                              `status`,

                              `TERMINAL_TURN_TIME_NM`

                              FROM `cliksense`.`gatemoves`;


                              /************************************************

                              Creating the bridge table

                              ************************************************/


                              LinkTable:

                              LOAD

                              m_row_id,

                                  visit,

                                  container,

                                  Timestamp([TET],'M/D/YYYY h:mm:ss[.fff] TT') as TimeStamp,  

                                'move' as type,

                                  If(Time(Frac([TET])) >= ('06:00:00 AM') and Time(Frac([TET]))< ('02:00:00 PM'), 'Morning',

                                  If(Time(Frac([TET])) >= ('02:00:00 PM') and Time(Frac([TET])) < ('10:00:00 PM'), 'Afternoon',

                                  If(Time(Frac([TET])) >= ('10:00:00 PM') and Time(Frac([TET])) < ('11:59:59 PM'), 'Night',

                                  If(Time(Frac([TET])) >= ('12:00:00 AM') and Time(Frac([TET])) < ('06:00:00 AM'), 'Night' ,0   )))) as shift   

                              Resident Moves

                              ;

                              Concatenate(LinkTable)

                              LOAD

                              v_row_id,

                                  visit,

                                  container,

                                  Timestamp(`EST_DPTR_D`,'M/D/YYYY h:mm:ss[.fff] TT') as TimeStamp,     

                              'vessel'  as type,

                                  If(Time(Frac(`EST_DPTR_D`)) >= ('06:00:00 AM') and Time(Frac(`EST_DPTR_D`))< ('02:00:00 PM'), 'Morning',

                                  If(Time(Frac(`EST_DPTR_D`)) >= ('02:00:00 PM') and Time(Frac(`EST_DPTR_D`)) < ('10:00:00 PM'), 'Afternoon',

                                  If(Time(Frac(`EST_DPTR_D`)) >= ('10:00:00 PM') and Time(Frac(`EST_DPTR_D`)) < ('11:59:59 PM'), 'Night',

                                  If(Time(Frac(`EST_DPTR_D`)) >= ('12:00:00 AM') and Time(Frac(`EST_DPTR_D`)) < ('06:00:00 AM'), 'Night',0)))) as shift       

                              Resident Vessel

                              ;

                              Concatenate(LinkTable)

                              LOAD

                              g_row_id,

                                  container,

                              Timestamp([TRACTOR_DEPART_DATE],'M/D/YYYY h:mm:ss[.fff] TT') as TimeStamp, 

                              'gate'  as type,

                                  If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('06:00:00 AM') and Time(Frac([TRACTOR_DEPART_DATE]))< ('02:00:00 PM'), 'Morning',

                                  If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('02:00:00 PM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('10:00:00 PM'), 'Afternoon',

                                  If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('10:00:00 PM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('11:59:59 PM'), 'Night',

                                  If(Time(Frac([TRACTOR_DEPART_DATE])) >= ('12:00:00 AM') and Time(Frac([TRACTOR_DEPART_DATE])) < ('06:00:00 AM'), 'Night',0)))) as shift       

                              Resident Gate

                              ;

                              DROP FIELD container, visit  FROM Vessel, Moves, Gate;

                              DERIVE FIELDS FROM FIELDS [TimeStamp],[TRACTOR_DEPART_DATE],[TET],[TRACTOR_ARRIVE_DATE],[WSTH], [COMP_MOVE],[LASTUPDATE],[DEPARTURE_LAST_LINE_TM], [EST_ARV_D], [EST_DPTR_D], [VSL_BERTH_D], [ACTL_ARV_D], [WORK_START_TM], [WORK_END_TM], [CNTR_ARRIVE_TM], [CNTR_DEPART_TM] USING [Calendar] ;

                          • Re: Access flags from canonical date table in set analysis
                            Robert Hutchings

                            Hi

                             

                            I would not set thing up like you have above.

                             

                            But if it works now great.

                             

                            It's so tiresome to work with the formula with my knowledge. For example, I got the above formula working but then, I am using it in the graph to show the trend and everything crumbles. Banging my head against the wall.

                             

                            I really don't know enough about your model yet. For example what is the relationship between the Vessel , moves and gate table. etc. I assume a container is one container load reference. Not a fixed container number

                             

                            But have you tried concatenating the three tables adding in the information in your link tables. Plus a date(floor( --- )) in each table. and link this to a calendar eg Date(floor(`EST_DPTR_D`,)) as CalendarDate, (or whatever name you want to call it (I would not use derived dates).


                             


                              • Re: Access flags from canonical date table in set analysis
                                Ekaterina Ponkratova

                                Robert, and what are my main mistakes looking at the script?

                                 

                                Robert, I am not asking for the details, but in general, as the consultation like this, I assume, could be considered as consulting advice which should be paid for.

                                  • Re: Access flags from canonical date table in set analysis
                                    Robert Hutchings

                                    Hi Ekaterina

                                     

                                    I don't know if its a mistake or not as I don't know enough about your data. But if it work what is the issue. But if not maybe try changing things as I have noted below

                                     

                                    Join data tables or Concatenate data tables

                                     

                                    I don't do a  bit of both (only part concatenate and join the linktables to the various data tables)  as you have done. I know some do but to me it just complicates the model. But I don't see why it shouldn't work. (Maybe I will try this approach in the future. At least it shows more detail in the data model)

                                     

                                    Derived Dates


                                    Try taking this out and adding a Date to the link table (Date(floor( ----- )) as etc and joining a calendar to the link table. Not to the other fields as well.

                                     

                                    Or adding a  date field to the linktables >> (Date(floor( ----- )) . Although [TimeStamp] may work. It creates issues with a non derived date calendar. I'm unsure if likewise with the derived date calendars or not

                                     

                                    but I don't like lots of derived calendars (as users can use the wrong calendar option). Do you need so many? And in general I don't like derived dates.

                                     

                                    DERIVE FIELDS FROM FIELDS [TimeStamp],[TRACTOR_DEPART_DATE],[TET],[TRACTOR_ARRIVE_DATE],[WSTH], [COMP_MOVE],[LASTUPDATE],[DEPARTURE_LAST_LINE_TM], [EST_ARV_D], [EST_DPTR_D], [VSL_BERTH_D], [ACTL_ARV_D], [WORK_START_TM], [WORK_END_TM], [CNTR_ARRIVE_TM], [CNTR_DEPART_TM] USING [Calendar]


                                    Aggr


                                    Don't use if avoidable  


                                    Did my adjusted expression work


                                    sum({$<type={'vessel'}>}

                                    (TimeStamp-VSL_BERTH_D)  *24)

                                     

                                    RowNo()

                                     

                                    RowNo() as g_row_id, etc


                                    I always use say


                                    'Gate' & RowNo() as g_row_id etc


                                    as for some reason in the past I ended up with the same rowno() in two tables. So by putting a 'Gate' or 'g' at the beginning it prevents this (but as they model is used now it makes no difference)