Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cluscombe
New Contributor III

Linking Master Calendar data to transaction data with multiple dates

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; 

Tags (1)
1 Solution

Accepted Solutions

Re: Linking Master Calendar data to transaction data with multiple dates

Have a look here: Canonical Date

- Marcus

1 Reply

Re: Linking Master Calendar data to transaction data with multiple dates

Have a look here: Canonical Date

- Marcus