Calendar Question

[xyz_1011]

Hi all,

i am trying to solve the following challange: I have a table (transactions) that contains a transaction date (various dates). I now want to build a calendar table that contains one date field that connects to the transaction table and one other field that functions as a "As Of" field. Example:

Transactions:

%TransactionDate	Amount
2021-04-16	$ 100
2021-04-15	$ 200
2021-04-15	$ 300
2021-04-14	$ 200
2021-04-13	$ 50

Calendar:

As_Of	%TransactionDate
2021-04-16	2021-04-16
2021-04-16	2021-04-15
2021-04-16	2021-04-14
2021-04-16	2021-04-13
2021-04-15	2021-04-15
2021-04-15	2021-04-14
2021-04-15	2021-04-13
2021-04-14	2021-04-14
2021-04-14	2021-04-13
2021-04-13	2021-04-13

What i am after is: For any given selected As_Of, i want to get all transactions with a transaction date equal or smaller to the selected As_Of date. I do want to try this building a calendar table (vs a set expression approach in the UI)

Thanks in advance!

[MayilVahanan · Accepted solution]

HI @xyz_1011 

Try like below

T1:
LOAD * INLINE [
%TransactionDate, Amount
2021-04-16, $ 100
2021-04-15, $ 200
2021-04-15, $ 300
2021-04-14, $ 200
2021-04-13, $ 50
];

T2:
Load Distinct %TransactionDate Resident T1;
Join
Load Distinct %TransactionDate as AsOfDate Resident T1;

NoConcatenate
Load * Resident T2 Where AsOfDate <= %TransactionDate;

DROP Table T2;

Screenshot: