7 Replies Latest reply: Feb 20, 2016 5:24 AM by Evgeny Stuchalkin RSS

    Need help with Canonical date. What i'm doing wrong?

    Evgeny Stuchalkin

      Hello! All details in video

       

      Using this method:

      https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

       

      What is my problem now:

      e8ffa465b8.jpg

      920c28b959.jpg

       

      897981d85a.jpg

       

      My code, if you want to run it on your machine (not required any SETtings)

      SET DateFormat='DD.MM.YYYY';

       

       

      Deals:

      LOAD * INLINE [Price , DealDay , MonthInPack, OrderLineID, DealID

         

      6000, 18.02.2016,6,1,1

      5000, 18.02.2016,4,2,2

      ];

       

       

      Accural:

      LOAD

        DealID,

        Price / MonthInPack AS AccuralSum,

          Date( AddMonths(DealDay, iterno()-1) ) AS AccuralDay

      RESIDENT Deals

      WHILE iterno() <= MonthInPack;

       

       

       

       

      DealID2DealDay:

      MAPPING LOAD

      DealID,

      DealDay

      RESIDENT Deals;

       

      DealID2AccuralDay:

      MAPPING LOAD

      DealID,

      AccuralDay

      RESIDENT Accural;

       

       

      DateBridge:

       

       

      Load OrderLineID, Applymap('DealID2DealDay',DealID,Null()) as CanonicalDate, 'Order' as DateType

      Resident Deals;

      Load OrderLineID, Applymap('DealID2AccuralDay',DealID,Null()) as CanonicalDate, 'Accural' as DateType

      Resident Deals;

        • Re: Need help with Canonical date. What i'm doing wrong?
          Gysbert Wassenaar

          You only load records from the Deals table into the DateBridge table. Since there are only two dates in the Deals table you get only two dates in the DateBridge table.

          • Re: Need help with Canonical date. What i'm doing wrong?
            Evgeny Stuchalkin

            Finaly, win .

            1ef3910f94.jpg

             

            Resume:

            1) Data Model. I moved field 'AccuralSum' to Deals Table. Instead of placing this value in Accural table.

            It gives to Qlik opportunity to count one Accural value per Deal in AccuralDay via cannoincal date. Instead of summing all 'AccuralSum's at each canonical date (like in screen above this post).

             

            2) DataBridge. I've changed mapping rules. First mapping rules was like this (taked it blindly from post with example):

             

            DealID2DealDay:

            MAPPING LOAD

            DealID,

            DealDay

            RESIDENT Deals;

             

            DealID2AccuralDay:

            MAPPING LOAD

            DealID,

            AccuralDay

            RESIDENT Accural;

             

            It dont works, because one deal have multiple 'AccuralDay' values, and applying this map just return to me first accural date for each deals (thanks to gwassenaar form helping me to realise it. ). It works properly only if you have only one date of each type for one deal.

             

            So, instead loading date via Applymap, i'm loading OrderLineID, to make connections between OrderLineID and accural date, because one OrderLineID always have one DealID:

             

            OrderLineID2DealID:

            MAPPING LOAD

            DealID,

            OrderLineID

            RESIDENT Deals;

             

             

            DateBridge:

            Load OrderLineID, DealDay as CanonicalDate, 'Order' as DateType

            Resident Deals;

            Load Applymap('OrderLineID2DealID',DealID,Null()) as OrderLineID, AccuralDay as CanonicalDate, 'Accural' as DateType

            Resident Accural;


            Final Code:


            SET DateFormat='DD.MM.YYYY';

             

             

            Deals:

            LOAD * INLINE [Price , DealDay , MonthInPack, OrderLineID, DealID, AccuralSum

              

            6000, 18.02.2016,6,1,1234,1000

            5000, 18.02.2016,4,2,2456,1250

            ];

             

             

            Accural:

            LOAD

              DealID,

              

                Date( AddMonths(DealDay, iterno()) ) AS AccuralDay

            RESIDENT Deals

            WHILE iterno() <= MonthInPack;

             

             

            OrderLineID2DealID:

            MAPPING LOAD

            DealID,

            OrderLineID

            RESIDENT Deals;

             

             

            DateBridge:

             

             

            Load OrderLineID, DealDay as CanonicalDate, 'Order' as DateType

            Resident Deals;

            Load Applymap('OrderLineID2DealID',DealID,Null()) as OrderLineID, AccuralDay as CanonicalDate, 'Accural' as DateType

            Resident Accural;

            Calendar code:

            QuartersMap: 

            MAPPING LOAD  

            rowno() as Month, 

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

            AUTOGENERATE (12); 

               

            Temp: 

            Load 

            min(CanonicalDate) as minDate, 

            max(CanonicalDate) as maxDate 

            Resident [DateBridge]; 

                 

            Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

            Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

            DROP Table Temp; 

                 

            TempCalendar: 

            LOAD 

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

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

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

                 

            [CanonicalCalendar]: 

             

             

            Load 

              date(TempDate, 'D MMM YY') AS CanonicalDate, 

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

              Year(TempDate) As [CanonicalYear], 

              date(MonthStart(TempDate),'MMM-YY') As [CanonicalMonth], 

              ApplyMap('QuartersMap', month(TempDate), Null()) as [CanonicalQuarter]

             

             

             

             

             

            Resident TempCalendar 

            Order By TempDate ASC; 

            Drop Table TempCalendar;