Count in Canonical Date doesn't give correct values

[Anonymous]

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', '');

[MK9885]

Not sure if this would work but try.... or if you can provide a sample xl data to work?

Vessel:

LOAD

[idvessl],

0 as [idmove],

0 as [TET],

`EST_DPTR_D` ,

`CNTR_NUM` as Container

,'Container_Vessels' as Flag2

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

trim(date(`EST_DPTR_D`,'YYYYMMDD')) as [DateID],    

,'Date_vessel'  as Flag

FROM [lib://Desktop/vesel_load.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

Moves:

LOAD

0 as [idvessl],

0 as [TRACTOR_DEPART_DATE]

[idmove],

[CONTAINER] as Container,

[TET]

,'Container_Moves' as Flag2

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

trim(date([TET],'YYYYMMDD')) as [DateID],

,'Date_moves' as Flag

FROM [lib://Desktop/move_vessel_upload.xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate

Gate:

LOAD

[idgate],

0 as [idmove],

0 as [TET],

0 as 'EST_DPTR_D',

0 as [idvessl],

[CNTR] as Container,

[TRACTOR_DEPART_DATE]

,'Container_Gates' as Flag2

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

trim(date([TRACTOR_DEPART_DATE],'YYYYMMDD')) as [DateID],

,'Date_gate'  as Flag

FROM [lib://Desktop/gate_10.xlsx]

(ooxml, embedded labels, table is Sheet1);

Master Calendar:

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3)  as Quarter

AUTOGENERATE (12); 

varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD'));

// varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));

varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

TempCalendar: 

LOAD

$(varMinDate) + Iterno()-1 as Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar: 

LOAD

*,

if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,

AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load 

trim(date(TempDate,'YYYYMMDD')) as [DateID],

date(TempDate,'DD/MM/YYYY') as [Effective Date],

if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

day(TempDate) as Day,

TempDate as [US Calendar Format],

date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],

date(TempDate,'WWWW') as [Full Day Name],

year(TempDate) as Year,

inyear(TempDate,today(),0) * -1  as [CY],    // Current Year

inyear(TempDate,today(),-1) * -1 as [First PY],

inyear(TempDate,today(),-2) * -1 as [Second PY],

inyeartodate(TempDate,today(),0) * -1  as [CYTD],

inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,

if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,

if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,

if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Ceil(Month(TempDate)/3) as [Quarter Number],

quarterName(TempDate) as [Quarter Name],

yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)  as [Quarter Year],

inquarter(TempDate,today(),0) * -1 as [CQ],  // Current Quarter

inquartertodate(TempDate,today(),0) * -1 as [CQTD],

inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

date(monthstart(TempDate),'MM') as [Month Number],

num(month(TempDate)) as Num_Month,

month(TempDate) as Month,                

date(monthstart(TempDate),'MMMM') as [Month Full Name],

monthstart(TempDate) as [Calendar Month Start Date],

monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

week(TempDate) as Week,

week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

week(weekstart(TempDate)) & '-' & Month(TempDate)    as [Week Month],

weekDay(TempDate) as [Week Day],

WeekEnd(TempDate) as WEEKENDS

Resident TempCalendar 

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table  QuarterNAME;

[Anonymous]

I have updated the original post.

What I expect is to see, the count for vessels for Jan: 39310

the count for gate for Jan: 38206

I don't want to concatenate the original tables because in fact, they are much bigger - the vessel has 27 fields and gate, 15. But for the sake of the argument, I deleted all the fields. Does my logic make sense?

[vitaliichupryna]

Ekaterina,

You shouldn't concatenate original tables, but should concatenate Link tables

Thanks,

Vitalii

[Anonymous]

Hi Vitalii,

That's what I was doing if I am not mistaken - remember that I am a novice - with the below part:

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

;

[Anonymous]

I think I have it. Let me test tomorrow but it seems that is it.

When did I become a full-time BI developer?

[Anonymous]

I partly got it work. Below is the script. I am using an autocalendar because then I will load data from SQL and I need declare fields for Qlik to recognize the TimeStamp.

There is one problem, though:

My understanding is that for me to access the data from the Link table, I need to select, for example, January,

and use something like

KPI1:

=count({<Flag={'Date_gate'}>} Container) in order to count how many containers are in the Gate table as of January

KPI2:

=count({<Flag={'Date_vessel'}>} Container) to count how many containers are in the Vessel table as of January.

The issue is that unless I add the dimension Flag2, the count doesn't work correctly. See attached the count with the Flag added and w/o the flag. What's the problem?

With the flag

Without the flag

AutoCalendar:

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

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

[GATE_VISIT_ID],

[CNTR] as Container,

[TRACTOR_DEPART_DATE],

FROM [lib://Desktop/gate_10.xlsx]

(ooxml, embedded labels, table is Sheet1);

LinkTable:

LOAD

Container

,'Container_Moves' as Flag2 

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

  ,'Date_moves' as Flag

Resident Moves

;

Concatenate(LinkTable)

LOAD

Container

,'Container_Gates' as Flag2

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

,'Date_gate'  as Flag    ,

    [GATE_VISIT_ID] as visit_gate

Resident Gate

;

Concatenate(LinkTable)

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

[robert99]

Hi

Is there only one row for each container in all the various tables.

I think what you need to do is select the table with the finest grain. And link this to the Link Table. But you need to ensure that this finest grain table has every container in it in the other two tables.

I would recommend doing this using new row() field NOT using container. And use mapping load to set up you link table for the two tables that do not have the finer grain

So it will be

Calendar <-- Joined -- > LinkTable  <-- Joined by field Row() --> Finest grain table <  Joined by Container > other tables

[robert99]

Ive explained all of this in the comments in the canonical post by Henric

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date#start=100

"i don't know which dates i should load directly"

Example

If there are two dates. One in a call table (initial call) and one in a  FSR table (field service request) So there is only ever one row / entry for one call (Call table = SCCall). But there can be many FSRs for one call (FSR table = SCFSR). The Call table and FSR table are joined by the Call_Num.

So the 'Date bridge' must join to the FSR table (finer grain) not the call table

//link table for required dates

DateBridge:  // FSR table

Load

CallFSR_LINK,     // join to the FSR table. This = Call_Num & FSR_Num

VisitDate AS DateC,   // Canonical Date

'Visit' as DateType      // to use in set analysis

Resident SCFSR

;

DateBridge:   //call table

Concatenate (DateBridge)

Load

CallFSR_LINK,  // join to the FSR table

ApplyMap ('MAPCALLINDATE'    ,Call_Num    ,'NoDate') as DateC,  //canonical Date from SCCall

'Call' as DateType     // to use in set analysis

resident SCFSR ;

NB If all dates are in the same table then load direct

[Anonymous]

Hi Robert,

Thank you for the response! I was actually trying with ids from every table as ids there are unique.

Sorry, just to repeat of what you are saying - remember that I am not very experienced.

1. Concatenate three tbls using Row()

LinkTable:

LOAD

Row 

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

  ,'Date_moves' as Flag

Resident Moves

;

Concatenate(LinkTable)

LOAD

Row

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

,'Date_gate'  as Flag  

Resident Gate

;

Concatenate(LinkTable)

LOAD

Row

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

,'Date_vessel'  as Flag

Resident Vessel

2. Concatenate LinkTable & Moves table (it's a granular enough).

LOAD *

From LinkTable

Concatenate

LOAD *

From Moves?

I was thinking about ApplyMap but it assumes one to many relationship while 1 container could have several gate visits in the gate table ie. several truck departure; several berth date in the vessel table and several moves in the move table.