Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There,
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.)
ItemNum
ItemClass
etc...
I then have a Fact table: (e.g.) -- Made up of about separate 8 tables
TransID
TransValue
ItemNum
etc...
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.
e.g.
ItemsMaster:
NOCONCATENATE LOAD *
RESIDENT Items
ORDER BY ItemNum;
CONCATENATE
LOAD Distinct ItemNum,
'No Class for Item' As ItemClass
Resident Fact
WHERE NOT EXISTS(ItemNum);
INNER JOIN (Items)
LOAD * RESIDENT Items;
drop Table Items;
Can't get it to work....
Is there a better way?
Thanks
Hi,
Since you have both of your Item and Fact tables already loaded, you can proceed in this way:
Item:
Load ItemID,
ItemID as ItemID2, //renaming field to differenciate with Fact
Class,
etc...
from...
Fact:
Load ItemID,
etc...
from...
concatenate(Item)
Load distinct ItemID,
'No class' as Class
resident Fact
where not exists(ItemID2, ItemID);
drop field ItemID2;
Hope this helps.
Hi,
Since you have both of your Item and Fact tables already loaded, you can proceed in this way:
Item:
Load ItemID,
ItemID as ItemID2, //renaming field to differenciate with Fact
Class,
etc...
from...
Fact:
Load ItemID,
etc...
from...
concatenate(Item)
Load distinct ItemID,
'No class' as Class
resident Fact
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)
Load Distinct
ItemNum,
'No Class' AS ItemClass
Resident Fact
Where Not Exists(ItemTemp, ItemNum)
;
Drop Field ItemTemp;