Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following two tables:
TABLE A-
Mem | Date |
---|---|
123 | 20/01/2016 |
123 | 25/01/2017 |
TABLE B-
Mem | Date |
---|---|
123 | 15/5/2016 |
123 | 16/7/2016 |
123 | 17/7/2016 |
123 | 01/01/2017 |
123 | 14/02/2017 |
123 | 20/02/2017 |
I need to add a column in table B based on table A. Final table B should look like:
Mem | Date | MemKey |
---|---|---|
123 | 15/5/2016 | 12316 |
123 | 16/7/2016 | 12316 |
123 | 17/7/2016 | 12316 |
123 | 01/01/2017 | 12316 |
123 | 14/02/2017 | 12317 |
123 | 20/02/2017 | 12317 |
My logic is that if date in table A is less than that of B. Then those dates hase to be mapped to that date until a greater date is encountered. in other words my dates in B has to be mapped to that of A if table B date is greater than that of A.
May be this
TableATemp:
LOAD * INLINE [
Mem, Date
123, 20/01/2016
123, 25/01/2017
];
TableA:
LOAD Mem,
Date as Start,
Mem & Right(Year(Date), 2) as MemKey,
Date(If(Mem = Previous(Mem), RangeSum(Previous(Date), -1), Today())) as End
Resident TableATemp
Order By Mem, Date desc;
DROP Table TableATemp;
TableB:
NoConcatenate
LOAD * INLINE [
Mem, Date
123, 15/5/2016
123, 16/7/2016
123, 17/7/2016
123, 01/01/2017
123, 14/02/2017
123, 20/02/2017
];
Left Join (TableB)
IntervalMatch(Date, Mem)
LOAD Start,
End,
Mem
Resident TableA;
Left Join (TableB)
LOAD *
Resident TableA;
DROP Table TableA;
May be this
TableATemp:
LOAD * INLINE [
Mem, Date
123, 20/01/2016
123, 25/01/2017
];
TableA:
LOAD Mem,
Date as Start,
Mem & Right(Year(Date), 2) as MemKey,
Date(If(Mem = Previous(Mem), RangeSum(Previous(Date), -1), Today())) as End
Resident TableATemp
Order By Mem, Date desc;
DROP Table TableATemp;
TableB:
NoConcatenate
LOAD * INLINE [
Mem, Date
123, 15/5/2016
123, 16/7/2016
123, 17/7/2016
123, 01/01/2017
123, 14/02/2017
123, 20/02/2017
];
Left Join (TableB)
IntervalMatch(Date, Mem)
LOAD Start,
End,
Mem
Resident TableA;
Left Join (TableB)
LOAD *
Resident TableA;
DROP Table TableA;
Data:
LOAD
Mem,
MinDate,
MaxDate,
date(MinDate+IterNo()-1) as Date,
1 as Flag
While MinDate+IterNo()-1<=MaxDate;
LOAD Mem,
date(min(Date)) as MinDate,
date(max(Date)-1) as MaxDate
Group by Mem;
LOAD * INLINE [
Mem, Date
123, 20/01/2016
123, 25/01/2017
];
right Join(Data)
LOAD * INLINE [
Mem, Date
123, 15/5/2016
123, 16/7/2016
123, 17/7/2016
123, 01/01/2017
123, 14/02/2017
123, 20/02/2017
];
Left Join(Data)
LOAD Distinct Mem,
Mem&date(max(MinDate),'YY') as MinMemKey,
Mem&date(max(MaxDate),'YY') as MaxMemKey
Resident Data
Group by Mem;
Final:
NoConcatenate
LOAD Mem,
Date,
if(Flag=1,MinMemKey,MaxMemKey) as MemKey
Resident Data;
DROP Table Data;
Maybe This:
TableA:
Load
Mem,
Date AS Date_A,
Mem & Right(Year(Date), 2) as MemKey
From tableA;
Left Join( TableA)
//TableB
Load
*
From TableB
Final:
Load
Mem,
Date,
If(Date>Date_A,Max(MemKey),Min(MemKey)) AS MemKey
Resident Table_A
Group by
Mem,
Date,
Date_A
Drop Table Table_A;
Thnaks for all the answers!
No problem... but is your question resolved? if it is then please close the thread by marking the correct and helpful responses...
Best,
Sunny