Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
avincesilao
Partner - Contributor
Partner - Contributor

Loading data to create a Cardinal Join Table

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

 

 

1 Solution

Accepted Solutions
javiersassen
Partner - Contributor III
Partner - Contributor III

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;)

View solution in original post

5 Replies
javiersassen
Partner - Contributor III
Partner - Contributor III

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
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor III
Partner - Contributor III

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
Partner - Contributor
Partner - Contributor
Author

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
Partner - Contributor III
Partner - Contributor III

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;)