Compare 2 dates from 2 different tables

[v_jaideep]

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.

[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

[v_jaideep]

@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

[v_jaideep]

@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 ID	start_date	Price		T.ID	approved date		Product ID	T.ID	PRODUCT NAME
1	11/1/2018	100		345	31/6/2019		1	345	A
2	15/6/2015	150		456	28/9/2017		2	456	B
3	24/8/2012	200		567	6/11/2010		3	567	C
4	12/12/2020	250		678	12/12/2011		4	678	D
5	18/1/2010	300		131	18/1/2010		5	131	E

Expected O/P
Product ID	Flag
1	0	start_date<approved date
2	0	start_date<approved date
3	1	start_date>approved date
4	1	start_date>approved date
5	1	start_date>approved date

[MayilVahanan · Accepted solution]

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: