Loading data to create a Cardinal Join Table

[avincesilao]

Hi, I was trying to load data by using the below code script but I get  the following error:

The memory limit for the request was reached.

The error occrur here:

[As-of Calendar]: Load Month as Month, AsofMonth as AsofMonth, Round((AsofMonth-Month)*12/365.2425) as MonthDiff, Year(AsofMonth)-Year(Month) as YearDiff Resident Month Where AsofMonth >= Month

Code script 

Let vMin = num(MakeDate(2018));
Let vMax = num(MakeDate(2018,12,31));

Temp:
Load Date($(vMin) + RowNo() -1) as Date
AutoGenerate 1
While date($(vmin) + RowNo() -1) < Date($(vMax));

Month:
Load Date(Monthstart(Date)) as Month
Resident Temp;

Join (Month)

Load Date(Monthstart(Date)) as AsofMonth
Resident Temp;

Drop table Temp;

[As-of Calendar]:
Load Month as Month,
AsofMonth as AsofMonth,
Round((AsofMonth-Month)*12/365.2425) as MonthDiff,
Year(AsofMonth)-Year(Month) as YearDiff
Resident Month
Where AsofMonth >= Month;

Drop Table Month;

Someone can help me to understand.

Thanks.

Antonio

[javiersassen]

your Month table contains 43.466² (close to 2.000.000.000) rows. I don't think this is what you intended. Can you specify what you try to accomplish? What should the final table look like and what do the date ranges have to be?

[avincesilao]

In attached what I'm trying to do.

I have been studying Qlik and I came across of this code script but something works wrong unless I have copied bad but I don't seems so.

Thanks.

Antonio

[javiersassen · Accepted solution]

Let vMin = num(MakeDate(2018));
Let vMax = num(MakeDate(2018,12,31));

Temp:
Load Date($(vMin) + RowNo() -1) as Date
AutoGenerate 1
While date($(vMin) + RowNo() -1) < Date($(vMax));

Month:
Load Distinct Date(Monthstart(Date)) as Month
Resident Temp;

Join (Month)

Load Date(Monthstart(Date)) as AsofMonth
Resident Temp;

Drop table Temp;

[As-of Calendar]:
Load Month as Month,
AsofMonth as AsofMonth,
Round((AsofMonth-Month)*12/365.2425) as MonthDiff,
Year(AsofMonth)-Year(Month) as YearDiff
Resident Month
Where AsofMonth >= Month;

Drop Table Month;

Sales:
LOAD 
    Date(Date#(Month,'MM/DD/YYYY')) as Month, 
    Sales 
inline [
Month, Sales
01/01/2018, 40363
02/01/2018, 43065
03/01/2018, 40276
04/01/2018, 29280
05/01/2018, 26784
06/01/2018, 28768
07/01/2018, 27690
08/01/2018, 22196
09/01/2018, 21813
10/01/2018, 25155
11/01/2018, 19962
12/01/2018, 29989
];

Take a close look at your script:

• Since in the autogenerate you referred to vmin while you defined vMin (capital M), it started to generate dates from 31-12-1899 instead of 1-1-2018.
• Second, it's best to load distinct values into your Month table. 
• last thing, there's no need to do Round((AsofMonth-Month)*12/365.2425) because you can just use Month(AsOfMonth) - Month(Month)

Hope this helps! Good luck;)

[avincesilao]

Thanks a lot for your answer, now everything work in the properly way.

Truly, I didn't  know the difference between vmin e vMin with the capital M. 

Regards.

Antonio 

[javiersassen]

if you declare a variable vMin, vmin isn't known to Qlik and in case of your script the following happened:

date($(vmin) + RowNo() -1) = date(0)

It's not so much that there's a difference between vmin and vMin, it's mainly that when you declare a variable with a certain notation, you have to call it in your while statement using the same notation. 'Let vmin = ' would've been a solution as well;)