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: 
v_jaideep
Creator
Creator

Compare 2 dates from 2 different tables

hi,

I have table A which has start_date and Table B which has approved_date . There is no key  b/w A and B. Using Table 'C' to join.I need to compare start date and approved date and return Sum({<DATE_FLAG = {'1'}>}sales). Here,Date_Flag ='1' specifies when start_date > approved date. I have tried to concatenate and compare but  doesn't work coz of the data I have. Any other suggestions.

1 Solution

Accepted Solutions
MayilVahanan

HI 

TA:
LOAD * INLINE [
Product ID, start_date, Price
1, 11/1/2018, 100
2, 15/6/2015, 150
3, 24/8/2012, 200
4, 12/12/2020, 250
5, 18/1/2010, 300
];
TB:
LOAD * INLINE [
T.ID, approved date
345, 31/6/2019
456, 28/9/2017
567, 6/11/2010
678, 12/12/2011
131, 18/1/2010
];

Join
LOAD * INLINE [
Product ID, T.ID, PRODUCT NAME
1, 345, A
2, 456, B
3, 567, C
4, 678, D
5, 131, E
];

Join(TA)
Load * Resident TB;

Final:
LOAD *, IF(start_date >= [approved date], 1, 0) as Flag Resident TA;

DROP Table TB, TA;

o/p:

MayilVahanan_0-1632284827816.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

5 Replies
MayilVahanan

Hi 

If you concatenate the tables, you can't see the start date and approved date on the same lines. 

Try with Join or ApplyMap based on the primary key, so that, you can see both values on the same lines and then you can able to create the Date_Flag in the scripting.

Hope it helps

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

@MayilVahanan I have tried join as well, but not working as expected . Date_flag values are not populating correctly. IF(start_date>= approved_date,1,0) as DATE_FLAG. Anything I'm missing here ?

MayilVahanan

Hi

Can you provide sample data and expected result

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

@MayilVahanan  below is sample data. When I join Table B and Table C "approved date" is good but when I join A and table B , "approved date" value changes. This is causing an  issue to get the desired O/P. 

Table A has Product ID,start_date,Price
Table B has T.ID,approved date
Tabel C has Product Id,T.ID,PRODUCT NAME

TABLE A TABLE B TABLE C
Product IDstart_datePrice T.IDapproved date Product IDT.IDPRODUCT NAME
111/1/2018100 34531/6/2019 1345A
215/6/2015150 45628/9/2017 2456B
324/8/2012200 5676/11/2010 3567C
412/12/2020250 67812/12/2011 4678D
518/1/2010300 13118/1/2010 5131E

 

Expected O/P 
   
Product IDFlag 
10start_date<approved date
20start_date<approved date
31start_date>approved date
41start_date>approved date
51start_date>approved date
MayilVahanan

HI 

TA:
LOAD * INLINE [
Product ID, start_date, Price
1, 11/1/2018, 100
2, 15/6/2015, 150
3, 24/8/2012, 200
4, 12/12/2020, 250
5, 18/1/2010, 300
];
TB:
LOAD * INLINE [
T.ID, approved date
345, 31/6/2019
456, 28/9/2017
567, 6/11/2010
678, 12/12/2011
131, 18/1/2010
];

Join
LOAD * INLINE [
Product ID, T.ID, PRODUCT NAME
1, 345, A
2, 456, B
3, 567, C
4, 678, D
5, 131, E
];

Join(TA)
Load * Resident TB;

Final:
LOAD *, IF(start_date >= [approved date], 1, 0) as Flag Resident TA;

DROP Table TB, TA;

o/p:

MayilVahanan_0-1632284827816.png

 

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