Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
waleeed_mahmood
Creator
Creator

Count Data After a date from another table

Hello all,

I hope you are doing well.

I have two tables as shown below. I want to sum the Amount of Table 2 only where the Transaction Date is greater than the approved date for that company. Please assume i have many columns in table 2 and I'm only showing a few here. I have added a column in Table 2 (To be Counted) that shows if the value should be counted based on expected results.

Please let me know if you have any questions and thank you for your help.

Table 1
CompanyApproved Date
Company 12020-03-17
Company 22020-11-15

 

Table 2
CompanyTransaction DateAmountTo be Counted:
Company 12020-03-121No
Company 12020-03-142No
Company 12020-03-163No
Company 12020-03-204Yes
Company 12020-03-215Yes
Company 12020-03-226Yes
Company 12020-03-237Yes
Company 12020-03-248Yes
    
Company 22020-11-099No
Company 22020-11-1010No
Company 22020-11-1711Yes
Company 22020-11-1812Yes
Company 22020-11-1913Yes
Company 22020-11-2014Yes
Company 22020-11-2115Yes

 

@sunny_talwar 

1 Reply
Saravanan_Desingh

Try this,

SET DateFormat='YYYY-MM-DD';

tab2:
LOAD * INLINE [
    Company, Transaction Date, Amount
    Company 1, 2020-03-12, 1
    Company 1, 2020-03-14, 2
    Company 1, 2020-03-16, 3
    Company 1, 2020-03-20, 4
    Company 1, 2020-03-21, 5
    Company 1, 2020-03-22, 6
    Company 1, 2020-03-23, 7
    Company 1, 2020-03-24, 8
    Company 2, 2020-11-09, 9
    Company 2, 2020-11-10, 10
    Company 2, 2020-11-17, 11
    Company 2, 2020-11-18, 12
    Company 2, 2020-11-19, 13
    Company 2, 2020-11-20, 14
    Company 2, 2020-11-21, 15
];

Left Join(tab2)

tab1:
LOAD * INLINE [
    Company, Approved Date
    Company 1, 2020-03-17
    Company 2, 2020-11-15
];

Left Join(tab2)

LOAD Company, Sum(Amount) As Sum_Amount
Resident tab2
Where [Transaction Date]>[Approved Date]
Group By Company;

commQV10.PNG