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;
