Joining on condition

[arpitkharkia]

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.

[sunny_talwar · Accepted solution]

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;

[Kushal_Chawda]

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;

[eduardo_dimperio]

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;

[arpitkharkia]

Thnaks for all the answers!

[sunny_talwar]

No problem... but is your question resolved? if it is then please close the thread by marking the correct and helpful responses...

Best,

Sunny