topic Re: Master Calendar with Crosstab Data in QlikView

Master Calendar with Crosstab Data

Wed, 25 Nov 2020 16:16:04 GMT

Hi! I am fairly new to Qlikview, but am trying to learn. I cannot figure out how to setup a Master Calendar for multiple crosstab tables.

My crosstab tables generally all look like the following:

ABSGPActuals:
CrossTable(Month, Actual, 11)
LOAD F1 as "#",
F2 as "Project",
F3 as "CFA",
F4 as "MktSegmt",
F72 as "Sbeg2",
F73 as "PM",
F74 as "Interco",
F76 as "Site",
'Airborne' as BU,
'2015' as Year,
'Gross Profit' as [Program Accounts],

January as "Jan",
February as "Feb",
March as "Mar",
April as "Apr",
May,
June as "Jun",
July as "Jul",
August as "Aug",
September as "Sep",
October as "Oct",
November as "Nov",
December as "Dec"

FROM
[$(vPathExternalData)Finance\2015ABS.xlsx]
(ooxml, embedded labels, table is [GP $], filters(
Remove(Row, Pos(Top, 10)),
Remove(Row, Pos(Top, 9)),
Remove(Row, Pos(Top, 8)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Replace(1, top, StrCnd(null))
))
WHERE
WildMatch (F76, 'Merrimack', 'Ft Worth');

On another tab I have combined all the different crosstab tables by doing the following:

NoConcatenate
Final:

Load * Resident ABSGPActuals;
Concatenate (Final)

Drop Table ABSGPActuals;

Below is the code I have been trying to make work for my master calendar .... with no luck:

//*************** MinMax Table *************
//Keeps minimum and maximum Date value from Facts table

MinMax:
LOAD
Min(FLOOR(Month)) as MinDate,
Max(FLOOR(Month)) as MaxDate

RESIDENT ABSNBActuals;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
//LET vToday = $(vMaxDate);
LET vToday = NUM(TODAY());

//*************** Temporary Calendar *************
//Generates a single table with one field containing all existing dates between MinDate and MaxDate.

TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*************** Master Calendar ***************
//Disconnected during the Date Island exercise by renaming TempDate as IslandDate

MasterCalendar:
LOAD
TempDate AS Month,
// Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month1,
// Day(TempDate) AS Day,
// Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Date(monthstart(TempDate), 'MMM-YY') AS MonthYY//,
// Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
// inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
// inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;
//

I am hoping someone can help! Thank you in advance!!!

Re: Master Calendar with Crosstab Data

sunny_talwar — Wed, 23 Mar 2016 20:03:52 GMT

May be try this:

Final:
LOAD *,

Date(MonthStart(Date#(Month & '-' & Year, 'MMM-YYYY'))) as Date

Resident ABSGPActuals;

DROP Table ABSGPActuals;

MinMax:
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Final;

LET vMinDate = Peek('MinDate', 0, 'MinMax');
LET vMaxDate = Peek('MaxDate', 0, 'MinMax');
LET vToday = Num(Today());

//*************** Temporary Calendar *************
//Generates a single table with one field containing all existing dates between MinDate and MaxDate.

TempCal:
LOAD Date($(vMinDate) + RowNo() - 1) AS TempDate
AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*************** Master Calendar ***************
//Disconnected during the Date Island exercise by renaming TempDate as IslandDate

MasterCalendar:
LOAD TempDate AS Date,
          Year(TempDate) AS Year,
          Month(TempDate) AS Month1,
          'Q' & ceil(month(TempDate) / 3) AS Quarter,
          Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
          Date(monthstart(TempDate), 'MMM-YY') AS MonthYY//,
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: Master Calendar with Crosstab Data

Wed, 23 Mar 2016 21:21:08 GMT

Thank you for your response! Do you have additional suggestions? ---- I tried your recommendation (with Master Calendar and then when I am loading resident tables), see code below, but I am getting the following errors:

//NoConcatenate

Final:

LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSNBActuals;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSSalesActuals;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSGPActuals;
//Resident ABSBLActuals;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSNBActuals2016;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSSalesActuals2016;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSGPActuals2016;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSMSActuals2016;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident ABSBPActuals2016;
//Load * Resident ABSRDActuals2016;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident AOP2016ABS;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident SFCSNBActuals;

//Load * Resident SFCSSalesActuals;
//Concatenate (Final)
//Load * Resident SFCSGPActuals;
//Concatenate (Final)
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident SFCSNBActuals2016;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident SFCSSalesActuals2016;

//LOAD * Resident SFCSGPActuals2016;
//Concatenate (Final)
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident AOP2016SFCS;

LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident SFCSBPActuals2016;

//LOAD * Resident SFCSMSActuals2016;
//Concatenate (Final)
//LOAD * Resident SFCSRDActuals2016;
//Concatenate (Final)
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident TALNBAct;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident SSSFTWNBAct;
LOAD *,
Date(MonthStart(Date#(Month&'-'&Year,'MMM-YYYY'))) as Date
Resident SSSSATNBAct;

Drop Table ABSNBActuals;
Drop Table ABSSalesActuals;
Drop Table ABSGPActuals;
//Drop Table ABSBLActuals;
DROP Table ABSNBActuals2016;
DROP Table ABSSalesActuals2016;
DROP Table ABSGPActuals2016;
DROP Table ABSMSActuals2016;
//DROP Table ABSBPActuals2016;
//DROP Table ABSRDActuals2016;
DROP Table AOP2016ABS;

Drop Table SFCSNBActuals;
//Drop Table SFCSSalesActuals;
//Drop Table SFCSGPActuals;
DROP Table SFCSNBActuals2016;
DROP Table SFCSSalesActuals2016;
//DROP Table SFCSGPActuals2016;
DROP Table SFCSBPActuals2016;
//DROP Table SFCSMSActuals2016;
//DROP Table SFCSRDActuals2016;
DROP Table AOP2016SFCS;

Re: Master Calendar with Crosstab Data

sunny_talwar — Wed, 23 Mar 2016 22:19:18 GMT

It is difficult to say what might be the issue... Would you be able to provide your complete script or qvw?

Re: Master Calendar with Crosstab Data

Wed, 23 Mar 2016 22:39:00 GMT

I cannot figure out how to upload the qvw to this string so I am going to start a new one - with the same title. Thank you so much for your help Sunny T! I appreciate it.

Re: Master Calendar with Crosstab Data

sunny_talwar — Thu, 24 Mar 2016 10:05:09 GMT

Here are the screenshots:

Step1:

Step2:

Re: Master Calendar with Crosstab Data

Thu, 24 Mar 2016 10:31:48 GMT

this is what I am seeing....

Re: Master Calendar with Crosstab Data

sunny_talwar — Thu, 24 Mar 2016 10:58:42 GMT

May be a browser issue, can you try another browser may be?

Re: Master Calendar with Crosstab Data

Chanty4u — Thu, 24 Mar 2016 11:00:44 GMT

can u open ur thread in other sheet?

it will looks the above screen hot which has mentioned by sunny

Re: Master Calendar with Crosstab Data

sunny_talwar — Fri, 25 Mar 2016 20:49:04 GMT

Can you try running the attached qvw. The biggest 2 changes I have made is, I moved Master Calendar script after Final table concatenation and I explicitly concatenated all the tables using Concatenate(Final) before every resident load.

Check it out

Re: Master Calendar with Crosstab Data

Peter_Cammaert — Fri, 25 Mar 2016 21:35:52 GMT

Do not reply to the thread in your Inbox, if you want to upload files. Instead, in your Inbox click the blue discussion title. Your discussion will be opened in full view, and you'll get an expanded set of functions and features when replying to a post. Including the "Use advanced editor" link Sunny mentioned.

Best,

Peter

Re: Master Calendar with Crosstab Data

Sat, 26 Mar 2016 12:05:28 GMT

Thank you Sunny! That worked!!!! I really appreciate your help!!!!

Re: Master Calendar with Crosstab Data

sunny_talwar — Sun, 27 Mar 2016 01:11:22 GMT

I am glad I was able to help you out Kelly