# Access flags from canonical date table in set analysis

**Ekaterina Ponkratova**Nov 4, 2018 4:15 AM

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;