Count Data After a date from another table

[waleeed_mahmood]

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
Company	Approved Date
Company 1	2020-03-17
Company 2	2020-11-15

Table 2
Company	Transaction Date	Amount	To be Counted:
Company 1	2020-03-12	1	No
Company 1	2020-03-14	2	No
Company 1	2020-03-16	3	No
Company 1	2020-03-20	4	Yes
Company 1	2020-03-21	5	Yes
Company 1	2020-03-22	6	Yes
Company 1	2020-03-23	7	Yes
Company 1	2020-03-24	8	Yes
Company 2	2020-11-09	9	No
Company 2	2020-11-10	10	No
Company 2	2020-11-17	11	Yes
Company 2	2020-11-18	12	Yes
Company 2	2020-11-19	13	Yes
Company 2	2020-11-20	14	Yes
Company 2	2020-11-21	15	Yes

@sunny_talwar 

[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;