Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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

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

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?

Partner
Partner

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

 

Partner
Partner

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

Partner
Partner

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 

 

Partner
Partner

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