Discussion Board for collaboration on QlikView Scripting.
I've been looking around and trying to do this but the examples I try just aren't working for me...
Basically I have Item Lookup table (e.g.)
I then have a Fact table: (e.g.) -- Made up of about separate 8 tables
Problem is that Item Numbers that exist in the Transaction Table that do not exist in the Item table.
I want to add to the Item Table the missing Items from my Transaction table and update the class value so that it is not null...
I want to have "No Class for Item" or something simlar to be the class of those items so it can be selected like a real class.
NOCONCATENATE LOAD *
ORDER BY ItemNum;
LOAD Distinct ItemNum,
'No Class for Item' As ItemClass
WHERE NOT EXISTS(ItemNum);
INNER JOIN (Items)
LOAD * RESIDENT Items;
drop Table Items;
Can't get it to work....
Is there a better way?
Go to Solution.
Since you have both of your Item and Fact tables already loaded, you can proceed in this way:
ItemID as ItemID2, //renaming field to differenciate with Fact
Load distinct ItemID,
'No class' as Class
where not exists(ItemID2, ItemID);
drop field ItemID2;
Hope this helps.
Thanks very much for your help...
When loading the items
I had put
ItemNum AS ItemNum,
ItemNum as ItemTemp
Then loaded the fact tables calling the item field ItemNum.
Then did this...
OUTER JOIN (Items)
'No Class' AS ItemClass
Where Not Exists(ItemTemp, ItemNum)
Drop Field ItemTemp;