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

# Access flags from canonical date table in set analysis

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?

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

;

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

;

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

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

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

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

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

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

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

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

;

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

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

LIB CONNECT TO [qlik_32];

Vessel:

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`,

`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`,

`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:

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

[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`,

`ACTV_REEF_S`,

`TEU`,

`LASTUPDATE`,

`VESSEL_VISIT_C`,

`CRANE`,

`TET`

FROM `cliksense`.`moves`;

Gate:

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

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

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

;

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

;

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

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

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

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

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

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

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)

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

Hi Robert,

Thank you for the feedback! If I survive this project, I will use your recommendations for another data model!