topic Conditional Outer Join in QlikView

Conditional Outer Join

loyarcec — Fri, 16 Mar 2018 19:38:19 GMT

Hello, does anyone could help me on this please?

I have something like this;

Table1:

Load

KEY

FROM (Excel 1)

Table2:

Load

KEY

FROM (Excel 2)

Both files have the same structure and there are many rows that are in both files, so I want to make a join between them but without generating duplicates.

Re: Conditional Outer Join

Digvijay_Singh — Fri, 16 Mar 2018 20:03:33 GMT

Use not exists with the key in the 2nd load, keep in mind not exists works in resident load or preceeding load only.

It will concatenate(not join) both the files. We use join normally when few fields are common between two tables. So it adds extra columns after join, after concatenate additional rows are normally added. But there are other complex cases for both join and concatenate features.

Table1:

Load

KEY

FROM (Excel 1)

Table2:

Load

KEY

FROM (Excel 2) Where not exists(KEY);

Re: Conditional Outer Join

loyarcec — Fri, 16 Mar 2018 20:48:24 GMT

Thanks!....it worked, with a little tweak though....I dug a little more about the function you mentioned and the method below worked.

Fiter_table:
LOAD Salesman&SalesmanName as Filter_Salesman_No,
SalesmanName as SM_name

FROM

(biff, embedded labels, table is Sheet1$);

// The First table should be your table which records needed to be filtered . Here we have to load the Duplicate table which is given by your Business

FinalTable:
LOAD Salesman,
SalesmanName,
Qty,
[Gross Price],
[Net Price],
Cost,
Profit,
Profit%,
inputfield
FROM

(biff, embedded labels, table is Sheet1$)

Where not Exists(Filter_Salesman_No,Salesman&SalesmanName);

Re: Conditional Outer Join

Digvijay_Singh — Fri, 16 Mar 2018 21:01:04 GMT

‌Great! Looks like you made the most out of this function.

Please close the thread and mark correct/helpful

answers if it helped.