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: 
xyz_1011
Partner - Creator
Partner - Creator

Calendar Question

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:

%TransactionDateAmount
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-162021-04-16
2021-04-162021-04-15
2021-04-162021-04-14
2021-04-162021-04-13
2021-04-152021-04-15
2021-04-152021-04-14
2021-04-152021-04-13
2021-04-142021-04-14
2021-04-142021-04-13
2021-04-132021-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!

Labels (2)
1 Solution

Accepted Solutions
MayilVahanan

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:

MayilVahanan_0-1620125036159.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

1 Reply
MayilVahanan

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:

MayilVahanan_0-1620125036159.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.