Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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