Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz_1011
Contributor II
Contributor II

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

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

View solution in original post