1 Reply Latest reply: Sep 17, 2015 10:45 AM by Marcus Sommer RSS

    Linking Master Calendar data to transaction data with multiple dates

    Chris Luscombe

      Hi Everyone -

      I have been using Qlik Sense for about two weeks, help out at noob!  Let me know if there a better way to ask this question.  I see that some people connect their actual Qlik Sense file, but now sure how to do that (and without showing you all my data).

       

      We were able to successfully use the master calendar function with another file that contained dates for transactions.  Hurray!

       

      But now I have transaction table that contains multiple date fields, and I struggling to makes the relationship between the multiple date fields and the master calendar.

       

      My transaction date load script looks like this:

       

      LIB CONNECT TO 'PRODLZ_ADHOC (corp_cluscombe)';

       

      LOAD "CLAIM_ID",

          "REASON_CODE",

          "RESOLUTION_TYPE",

          "RECOV_AMT",

          "PAYOUT_DATE",  //another date field!

          "PAYOUT_AMT",

          "PAYOUT_MSG_PARAMS",

          "TRANSACTION_CODE",

          "TRANSACTION_DATE",  //one date field!

          AMOUNT,

          "MESSAGE_PARAMS",

          "SWITCH_NAME",

          "MERCHANT_CATEGORY_CODE",

          "MERCHANT_INFO";

      SQL SELECT "CLAIM_ID",

          "REASON_CODE",

          "RESOLUTION_TYPE",

          "RECOV_AMT",

          "PAYOUT_DATE",

          "PAYOUT_AMT",

          "PAYOUT_MSG_PARAMS",

          "TRANSACTION_CODE",

          "TRANSACTION_DATE",

          AMOUNT,

          "MESSAGE_PARAMS",

          "SWITCH_NAME",

          "MERCHANT_CATEGORY_CODE",

          "MERCHANT_INFO"

      FROM BAN."PROC_PAYOUT_BY_DAY";

       

      And my master calendar load script look like this:

       

      LOAD

           Date (FieldValue ('DateNumber', RecNo())) AS Dates

      AUTOGENERATE

           FieldValueCount ('DateNumber');

       

       

      Date_Interval:

      LOAD

           Dates ,

           RowNo () AS RownNo

      RESIDENT

           Dates

      ORDER BY

           Dates ASC ;

      // exit Script;

      DROP TABLE Dates;

       

      LET vDateMin = Num (Peek ('Dates',  0, 'Date_Interval'));

      LET vDateMax = Num (Peek ('Dates', -1, 'Date_Interval'));

       

      DROP TABLE Date_Interval;

       

      TempCalendar:

      LOAD

           $(vDateMin) + RowNo () - 1 AS DateNumber,

           Date ( $(vDateMin) + RowNo() - 1) AS TempDate

      AUTOGENERATE

           1

      WHILE

           $(vDateMin) + IterNo () - 1 <= $(vDateMax);

       

       

      AsofCAL:

      LOAD

           DateNumber   ,

           Date (DateNumber ) AS TRANSACTION_DATE ,

           Day (DateNumber ) AS Day ,

           Week (DateNumber ) AS Week ,

           Month (DateNumber ) AS Month ,

           'Q' & Ceil (Month (DateNumber )/3) AS Quarter,

           Year (DateNumber ) AS Year ,

           IF (DateNumber = Today (),1,0) AS TodayFlag,

           Num (month (DateNumber )) AS MonthLink ,

           Weekday (DateNumber ) AS WeekDay ,

           Date (MonthStart (DateNumber ), 'MMM-YYYY') AS YearMonth,

           Dual (Num (Week (DateNumber ),00) & '-' & Year( DateNumber), Year (DateNumber ) & Num (Week (DateNumber ),00)) AS  WeekYear,

           IF (Year (DateNumber ) = Year( Today()),1,0) AS CurrYearFlag ,

           IF (Year (DateNumber ) = Year( Today())-1,1,0) AS PriorYearFlag ,

           Year2Date (DateNumber ,-1) * -1 AS PriorYTDFlag,

           Year2Date (DateNumber ) * -1 AS CurrYTDFlag

      //      IF(InWeekToDate (DateNumber ,Today (),0),1,0) AS CurrWTDFlag,

      //      IF(InWeekToDate (DateNumber ,Today (),-1),1,0) AS PriorWTDFlag,

      //      IF(InlunarWeekToDate (DateNumber ,Today (),0),1,0) AS CurrLunWTDFlag,

      //      IF(InlunarWeekToDate (DateNumber ,Today (),-1),1,0) AS PriorLunWTDFlag,

      //      IF(InMonthToDate (DateNumber ,Today (),0),1,0) AS CurrMTDFlag,

      //      IF(InMonthToDate (DateNumber ,Today (),-1),1,0) AS PriorMTDFlag,

      //      IF(InQuarterToDate (DateNumber ,Today (),0),1,0) AS CurrQTDFlag,

      //      IF(InQuarterToDate (DateNumber ,Today (),-1),1,0) AS PriorQTDFlag,

      //      IF(InQuarter (DateNumber ,Today (),-1),1,0) AS PriorQTRFlag,

      //      IF(InQuarter (DateNumber ,Today (),0),1,0) AS CurrQTRFlag

      RESIDENT

           TempCalendar

      ORDER BY

           TempDate ASC ;

       

      DROP TABLE TempCalendar;