topic Re: Join in QlikView in QlikView

Join in QlikView

soha1902 — Thu, 06 Aug 2015 05:56:10 GMT

Hi All,

I have two tables like

Table1

ProductID

ProductName

PDate

Table2

ProductID

CustName

StartDate

EndDate

Now I want to join these two tables based on following condition

if(Table1.ProductID = Table2.ProductID and Table1.PDate Between(Table2.StartDate,Table2.EndDate)

How I implement this join condition in QlikView??

I am using personal edition so please share the script.

Thanks

Re: Join in QlikView

vardhancse — Thu, 06 Aug 2015 06:00:08 GMT

Hi,

If you want to join in qlikview

Table1:

ProductID,

ProductName,

PDate;

Left Join(Table1)

ProductID,

CustName,

StartDate,

EndDate;

Based on ProductID both the table will be joined and the result will be in to one single table

Re: Join in QlikView

Gysbert_Wassenaar — Thu, 06 Aug 2015 06:02:02 GMT

You can use the IntervalMatch function:

Table1:

LOAD ProductID, ProductName, PDate

FROM ...

Table2:

LOAD ProductID, CustName, StartDate, EndDate

FROM ...

IntervalMatch( PDate, ProductID)

LOAD distinct ProductID, StartDate, EndDate

Resident Table2;

See this blog post for more information: IntervalMatch

Re: Join in QlikView

Kushal_Chawda — Thu, 06 Aug 2015 06:07:08 GMT

I think you need interval match here.

Please see the below link to achieve the same using interval match

https://community.qlik.com/thread/93656

Re: Join in QlikView

jonathandienst — Thu, 06 Aug 2015 06:09:45 GMT

Use an interval match like this:

// Load source data

Table1:

LOAD

ProductID

ProductName

PDate

FROM ....

Table2:

LOAD

ProductID

CustName

StartDate

EndDate

FROM ....

// Match the PDate to the intervals in Table2

Join(Table1)

IntervalMatch(PDate, ProductID)

LOAD StartDate, EndDate, ProductID

Resident Table2;

// Collapse result to single table (add CustName to Table1)

Join(Table1)

LOAD * Table2;

// Not needed any more

DROP Table Table2;

The final result in Table1 will be

ProductID

ProductName

PDate

CustName

StartDate

EndDate