Join in QlikView

[soha1902]

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

[vardhancse]

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

[Gysbert_Wassenaar]

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

[Kushal_Chawda]

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

[jonathandienst · Accepted solution]

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