Access flags from canonical date table in set analysis

[Anonymous]

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;

[Anonymous · Accepted solution]

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))

[robert99]

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

[Anonymous]

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.

[robert99]

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

[robert99]

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

[Anonymous]

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] ;

[robert99]

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

TimeStamp-VSL_BERTH_D  *24)

should be

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

(TimeStamp-VSL_BERTH_D)  *24)

as I assume VSL_BERTH_D is another timestamp

I avoid aggr unless I have no other choice. In this case it seems you do not need to use it

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

[robert99]

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).

[Anonymous]

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.