Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

arpitkharkia
New Contributor II

Joining on condition

Hi all,

I have the following two tables:

TABLE A-

MemDate
12320/01/2016
12325/01/2017

TABLE B-

MemDate

123

15/5/2016
12316/7/2016
123

17/7/2016

12301/01/2017
12314/02/2017
12320/02/2017

I need to add a column in table B based on table A. Final table B should look like:

MemDateMemKey

123

15/5/201612316
12316/7/201612316
123

17/7/2016

12316
12301/01/201712316
12314/02/201712317
12320/02/201712317

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Joining on condition

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;

Capture.PNG

5 Replies
MVP
MVP

Re: Joining on condition

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;

Capture.PNG

Re: Joining on condition

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_dimperi
Valued Contributor II

Re: Joining on condition

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
New Contributor II

Re: Joining on condition

Thnaks for all the answers!

MVP
MVP

Re: Joining on condition

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

Best,

Sunny

Community Browser