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: 
Not applicable

How to generate a list of values for all possible combinations

Hi guys
Hope you all are doing well.
I am busy building a mapping file for a P&L model, but are having problems with GL Accounts & Cost Centres which do not have values (null not zero) for specific date ranges.
You will see attached 2 files. One includes all the cost centre codes and the other one all the GL codes.
I need to generate a zero value (Field name = Amount) for all possible combinations of Cost Centres & GL Codes on every 1st day of a month from today up until 2 years in the past.
I have the following script which generates all the dates and a zero value per day, but I do not know how to only generate the first day of a month and also how to combine these dates and zerp values with the Cost Centres & GL Codes.
Hope that someone can help me.
Regards
Christo
LET
vDateMin = 39814; // 2009/01/01
LET vDateMax = num(date(Today())); // Today

TempCalendar:

LOAD
$(vDateMin) + RowNo() - 1 as DateNumber,
date($(vDateMin) + RowNo() - 1) as TempDate
AutoGenerate 1
While $(vDateMin) +IterNo()-1<= $(vDateMax) ;

[ALL VALUES]:

LOAD
date(TempDate,'YYYY/MM/DD') as [Posting Date],
0
as Amount
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like this:

LET

vDateMin = 39814; // 2009/01/01

LET vDateMax = num(date(Today())); // Today

TempCalendar:

LOAD

$(vDateMin)+ RowNo() - 1 as DateNumber,

date(monthstart(AddMonths($(vDateMin), RowNo() - 1))) as TempDate

AutoGenerate 1

While addmonths($(vDateMin),IterNo()-1 ) <= $(vDateMax) ;

CostCenter:

Directory;

LOAD [CC Code]

FROM

[Cost Centre Codes.xlsx]

(ooxml, embedded labels, table is Sheet1);

GL:

Directory;

join LOAD [GL Code]

FROM

[GL Codes.xlsx]

(ooxml, embedded labels, table is Sheet1);

 

[ALL VALUES]:

join LOAD

date(TempDate,'YYYY/MM/DD') as [Posting Date],

0 as Amount

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I used addmonths and monthstart function to get only zero on first days of a month, and used join which results in cartesian products (no common field values), so I think this might just be what you want.

Regards,

Stefan

View solution in original post

1 Reply
swuehl
MVP
MVP

Maybe like this:

LET

vDateMin = 39814; // 2009/01/01

LET vDateMax = num(date(Today())); // Today

TempCalendar:

LOAD

$(vDateMin)+ RowNo() - 1 as DateNumber,

date(monthstart(AddMonths($(vDateMin), RowNo() - 1))) as TempDate

AutoGenerate 1

While addmonths($(vDateMin),IterNo()-1 ) <= $(vDateMax) ;

CostCenter:

Directory;

LOAD [CC Code]

FROM

[Cost Centre Codes.xlsx]

(ooxml, embedded labels, table is Sheet1);

GL:

Directory;

join LOAD [GL Code]

FROM

[GL Codes.xlsx]

(ooxml, embedded labels, table is Sheet1);

 

[ALL VALUES]:

join LOAD

date(TempDate,'YYYY/MM/DD') as [Posting Date],

0 as Amount

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I used addmonths and monthstart function to get only zero on first days of a month, and used join which results in cartesian products (no common field values), so I think this might just be what you want.

Regards,

Stefan