How to generate a list of values for all possible combinations

[#1]

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;

[swuehl · Accepted solution]

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