topic Re: Joining on condition in QlikView

Joining on condition

arpitkharkia — Wed, 27 Sep 2017 09:13:53 GMT

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.

Re: Joining on condition

sunny_talwar — Wed, 27 Sep 2017 11:52:29 GMT

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;

Re: Joining on condition

Kushal_Chawda — Wed, 27 Sep 2017 13:17:03 GMT

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;

Re: Joining on condition

eduardo_dimperio — Wed, 27 Sep 2017 13:53:59 GMT

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;

Re: Joining on condition

arpitkharkia — Wed, 04 Oct 2017 11:46:42 GMT

Thnaks for all the answers!

Re: Joining on condition

sunny_talwar — Wed, 04 Oct 2017 11:49:47 GMT

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

Best,

Sunny