Hi guys,
I feel more and more comfortable with Qlik Sense but some parts are still confusing. For example, today I got the canonical dates working...Well...halfway.
There is a vessel table which has 39,691 records; gate table with 60,214 records and moves with 71,765 records. The concatenated DateLink table has 171,670 records.
But then, if I use =count({<Flag={'Date_gate'}, Flag2={'Container_Gates'}>} Container) or
=count({<Flag={'Date_moves'}, Flag2={'Container_Moves'}>} Container) and select a day using the date from the filter panel '=Date(Floor(Date(TimeStamp,'MM/DD/YYYY hh:mm:ss.ffff')),'DD/MM/YYYY')'
I am not getting the correct values. They are usually higher than they should be. If I don't select any date, the count gives lower values than it should be i.e. Count using Container_Moves, Container_Vessel and Container_Gate.
What am I doing wrong in my query?
Background: the vessel table contains information on the vessel visit i.e. one vessel visit could have many containers (container id) but one departure date (EST_DPTR_D).
The move table contains information on the container moves where [TET] is the hour of the move.
The gate table contains information on the time ([TRACTOR_DEPART_DATE]) a container left the port.
The requirement is to have one calendar on the front-end which will allow a user to pick a date and see the # of containers which were moved within the port (move table) and left the port (gate table). In theory, the number of moves in the vessel table should match with the number of moves in the moves table, so I use both tables interchangeable. For this reason, I named all three fields i.e. TET, EST_DPRT_D and TRACTOR_DEPART_DATE as TimeStamp. It was all good but I need to do some calculation which requires usage of columns from the vessel table and move table.
So, I looked into the canonical date feature. It looks good i.e. I can perform calculation using cols from both tables, but now I have an issue of bizzare count.
Vessel:
LOAD
[idvessl],
`EST_DPTR_D` ,
`CNTR_NUM` as Container
FROM [lib://Desktop/vesel_load.xlsx]
(ooxml, embedded labels, table is Sheet1);
Moves:
LOAD
[idmove],
[CONTAINER] as Container,
[TET]
FROM [lib://Desktop/move_vessel_upload.xlsx]
(ooxml, embedded labels, table is Sheet1);
Gate:
LOAD
[idgate],
[CNTR] as Container,
[TRACTOR_DEPART_DATE]
FROM [lib://Desktop/gate_10.xlsx]
(ooxml, embedded labels, table is Sheet1);
DateLink:
LOAD
Container
,'Container_Moves' as Flag2
,Timestamp([TET],'DD/MM/YYYY hh:mm:ss tt') as TimeStamp
,'Date_moves' as Flag
RESIDENT Moves
;
Concatenate
LOAD
Container
,'Container_Gates' as Flag2
,Timestamp([TRACTOR_DEPART_DATE],'DD/MM/YYYY hh:mm:ss tt') as TimeStamp
,'Date_gate' as Flag
RESIDENT Gate
;
Concatenate
LOAD
Container
,'Container_Vessels' as Flag2
,Timestamp(`EST_DPTR_D`,'DD/MM/YYYY hh:mm:ss tt') as TimeStamp
,'Date_vessel' as Flag
RESIDENT Vessel
;
//Creating a calendar
SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
LOAD
Date(Floor(Date([$(_field)],'MM/DD/YYYY hh:mm:ss.ffff')),'DD/MM/YYYY') AS [$(_prefix)Date],
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday]
,hour([$(_field)]) as [$(_prefix)Hour]
,minute([$(_field)]) as [$(_prefix)Minute]
;
LOAD
date(DateMin + IterNo()) as [$(_field)]
WHILE DateMin + IterNo() <= DateMax
;
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');
END SUB
CALL CalendarFromField('TimeStamp', 'CommonCalendar', '');
