Conditional Outer Join

[loyarcec]

Hello, does anyone could help me on this please?

  I have something like this;

Table1:

Load

    A

    KEY

    B

FROM (Excel 1)

Table2:

Load

    A

    KEY

    B

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.

[Digvijay_Singh]

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

    A

    KEY

    B

FROM (Excel 1)

Table2:

Load

    A

    KEY

    B

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

[loyarcec · Accepted solution]

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);

[Digvijay_Singh]

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

Please close the thread and mark correct/helpful

answers if it helped.