topic Re: Load Missing Values in Resident table into another table... in QlikView

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

Thu, 22 Nov 2012 07:35:12 GMT

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

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

Thu, 22 Nov 2012 09:01:58 GMT

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.

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

Fri, 23 Nov 2012 04:27:36 GMT

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;