Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

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.

2 Replies
Not applicable

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

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

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

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;

Community Browser