NOCONCATENATE LOAD DISTINCT trim(PRODUCT_FAMILY) as [Product Family], trim(PLANT) as [Product Plant], [PF:PLNT:PRD] RESIDENT [MMC HIST];
CONCATENATE([Product Filter Table])
LOAD DISTINCT trim(PRODUCT_FAMILY) as [Product Family], trim(PLANT) as [Product Plant], [PF:PLNT:PRD] RESIDENT [DSA Actual Consumption] WHERE NOT EXISTS(PF:PLNT:PRD);
CONCATENATE([Product Filter Table])
LOAD DISTINCT trim(PRODUCT_FAMILY) as [Product Family], trim(PLANT) as [Product Plant], [PF:PLNT:PRD] RESIDENT BOM WHERE NOT EXISTS(PF:PLNT:PRD);
The above expression is only fetching rows from first table, and the no rows are being fetched from the other following tables. I have checked and the data exists there. even the PF:PLNT:PRD key is different in those tables. then what could be the problem with the above statement. help plzz
okay, I just read in an article the where exists check if the data has already been loaded in the same field. so what if for example in the above expression, if i only want to see if the same data has been loaded in the above table and not in the whole script.
Is it possible to see if the same data in the same field has already been loaded or not in a specific table? for example in the above statement, I would like to see if the same data (PF:PLNT:PRD) has not been loaded in [Product Filter Table], then load the data otherwise ignore.
Please not that the same fields have already been loaded in other tables, but i only want to check in this specific table
WHERE NOT EXISTS is only going to load rows where the value of PF:PLNT:PRD does not exist in the field named PF:PLNT:PRD in ANY TABLE. Your second and third tables are loading from a resident table and all of your keys already exist in those resident tables, so the bottom two tables aren't going to load anything. What you need to do is change some of your key names like this:
If you really want the field to be called [PF:PLNT:PRD], change the name of that field in the [MMC HIST], DSA Actual Consumption], and [BOM] tables to something like PF:PLANT:PRD:TEMP or anything that isn't PF:PLANT:PRD
Then do the following: (hopefully the syntax is right)
[Product Filter Table]:
NOCONCATENATE LOAD DISTINCT trim(PRODUCT_FAMILY) as [Product Family], trim(PLANT) as [Product Plant], [PF:PLT:PRD:TEMP] as [PF:PLNT:PRD] RESIDENT [MMC HIST];
CONCATENATE([Product Filter Table])
LOAD DISTINCT trim(PRODUCT_FAMILY) as [Product Family], trim(PLANT) as [Product Plant],
[PF:PLT:PRD:TEMP] as [PF:PLNT:PRD] RESIDENT [DSA Actual Consumption] WHERE NOT EXISTS(PF:PLNT:PRD,[PF:PLT:PRD:TEMP]);
CONCATENATE([Product Filter Table])
LOAD DISTINCT trim(PRODUCT_FAMILY) as [Product Family], trim(PLANT) as [Product Plant],
[PF:PLT:PRD:TEMP] as [PF:PLNT:PRD]
RESIDENT BOM WHERE NOT EXISTS(PF:PLNT:PRD,[PF:PLT:PRD:TEMP]);
So the syntax is:
WHERE NOT EXISTS([name of existing field],[name of field being loaded]);
Notice how the where clauses uses the TEMP in the second argument, you just use the name of the field before it's renamed.
As far as I know you can't do that (maybe some complicated way with peek but I'm not sure). If you rename the tables like I described above, it should do basically the same thing.
Edit: I'm referring to your question about specifying a table name in WHERE NOT EXISTS
Sorry, I had not read your post when i was replying. Your solution seems to be the one i am looking for. Thanks. Let me try this and I will get back to you
I was just wondering. Even If i rename the keys to temp in the previous tables, does that not mean that they already exist in memory, so it wont be loaded again. I dont know but just a question?
Exists is only going to look in the field you tell it to. So for example if you put:
WHERE NOT exists(new_field_name,temp_field_name)
It is only going to look at values in the current table in the field temp_field_name and it is going to see if they exist in a field called new_field_name. Let's say you had another table with every single value of new_field_name and temp_field_name in a field called random_field_name, it wouldn't have any effect on your where clause.
If you really want to understand how the WHERE exists( and WHERE NOT exists( stuff works I suggest making a small app to test it. Just make a bunch of tables with various combinations of exists, not exists, etc. to see what happens. Here are a couple examples I already have done:
//MAPPING TABLES HAVE NO EFFECT ON EXISTS STATEMENTS
TableA:
MAPPING LOAD * INLINE [
id, map_flag
A, 1
B, 1
C, 0
];
TableC:
LOAD * INLINE [
id, map_flag
A, 1
B, 1
C, 0
];
DROP TABLES TableC;
TableD:
LOAD * INLINE [
id, map_flag
A, 1
D, 1
C, 0
];
//TableB loads only B even though it clearly exists in the mapping table, so mapping tables don't have any effect on exists.
TableB:
NOCONCATENATE LOAD * INLINE [
id
A
B
C
D
]
WHERE NOT exists(id);
DROP TABLES TableD;
//Another example
Table1:
LOAD * INLINE [
Name2, Age, Gender
Sarah, 23, F
Robert, 24, M
];
Table2:
LOAD * INLINE [
Name, Height
Tom, 6
];
//Table3 only loads Tom
Table3:
LOAD * INLINE [
Name, Weight
Tom, 155
Sarah, 109
Robert, 185
]
WHERE NOT exists(Name2,Name);
The second one is an example of what your problem was in the original post.
