Omit data in resident tables

[user467341]

Hi.

I have two resident tables called Table_Items and Table_Items_Excluded.

Table_Items

PK,

Item_Number,

Item_Name,

Qty

Table_Items_Exclude

Item_Number

What I would like to achieve is to exclude all the Item_Number that appears inside Table_Items_Exclude in Table_Items table. In other words, Table_Items must not have these Item_Number from Table_Items_Exclude.

I can't seems to figure out how to make the RESIDENT JOIN works in the data load editor. Any help?

[maheshkuttappa]

Load excludes table first rename Item_Number and load Table_Items with where not exist. Below is the code snippet 

Table_Items_Exclude:

Load

Item_Number as Item_Number_Exclude

Resident  <Table Name> :

Table_Items:

Load

PK,

Item_Number,

Item_Name,

Qty

Resident  <Table Name> where not exist (Item_Number_Exclude,item_Number);

[user467341]

Hi, does this directly affect my Table_Items table with your code snippet? I don't see a new table being created from this. 

[user467341]

Hi.

I did more testing using the code snippet, what I noticed is that when I use the snippet above, it makes my original tables double in data (it seems like duplicating the data again). The Table_Items does seem to have excluded the data from the Table_Items_Exclude.

Why is my data being doubled and how do I fix this?

Table_Items_Exclude:

Load

*

Resident  <Table Name> :

Table_Items:

Load

*

Resident  <Table Name> where not exist (Item_Number_Exclude,item_Number);

[vincent_ardiet_]

If you have the exact same columns in both table, Qlik will concatenate everything in the first one.
Look at @maheshkuttappa code, he is loading only one field in Table_Items_Exclude.

[user467341]

Hi Vincent.

I see. Then is it possible if I can do NOT EXISTS directly on my data load? For example:

Table_Item_Exclude:

Load abc_Item_Number AS Item_Number

FROM [lib://xxx]

(xxx)

Table_Item:

Load 

PK,

Item_Number,

Item_Name,

Qty;

SQL SELECT 

"PK",

"Item_Number",

"Item_Name",

"Qty";

FROM dbo.table_item;

[vincent_ardiet_ · Accepted solution]

Yes like this:

Table_Item_Exclude:
Load abc_Item_Number AS Item_Number_To_Exclude
FROM [lib://xxx]
(xxx);

Table_Item:
Load
PK,
Item_Number,
Item_Name,
Qty
Where Not Exists (Item_Number_To_Exclude,Item_Number);
SQL SELECT
"PK",
"Item_Number",
"Item_Name",
"Qty";
FROM dbo.table_item;

[Aasir]

LEFT JOIN statement and then filter out the rows where there is no match between the two tables.

Try this below

// Load Table_Items
Table_Items:
LOAD * INLINE [
PK, Item_Number, Item_Name, Qty
1, A123, Item 1, 10
2, B456, Item 2, 5
3, C789, Item 3, 8
];

// Load Table_Items_Exclude
Table_Items_Exclude:
LOAD * INLINE [
Item_Number
B456
C789
];

// Use a LEFT JOIN to exclude Item_Number values from Table_Items_Exclude
Table_Items_Excluded:
LOAD
PK,
Item_Number,
Item_Name,
Qty
RESIDENT Table_Items
LEFT JOIN (Table_Items_Exclude)
LOAD
Item_Number as Exclude_Item_Number
RESIDENT Table_Items_Exclude;

// Filter out rows where there is a match
NoConcatenate
Table_Items_Excluded:
LOAD
PK,
Item_Number,
Item_Name,
Qty
RESIDENT Table_Items
WHERE IsNull(Exclude_Item_Number);