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