Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load Missing Values in Resident table into another table...

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

2 Replies
Not applicable
Author

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.

Not applicable
Author

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;