28 Replies Latest reply: Nov 3, 2018 5:51 PM by Robert Hutchings RSS

    Count in Canonical Date doesn't give correct values

    Ekaterina Ponkratova

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

        • Re: Count in Canonical Date doesn't give correct values
          Shahbaz Khan Mohammed

          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;






          • Re: Count in Canonical Date doesn't give correct values
            Ekaterina Ponkratova

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

             

            When did I become a full-time BI developer?

            • Re: Count in Canonical Date doesn't give correct values
              Ekaterina Ponkratova

              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

              with_flag.png

              Without the flag

              wo_flag.png

              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

                • Re: Count in Canonical Date doesn't give correct values
                  Robert Hutchings

                  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

                    • Re: Count in Canonical Date doesn't give correct values
                      Robert Hutchings

                      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

                      • Re: Count in Canonical Date doesn't give correct values
                        Ekaterina Ponkratova

                        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.