topic Re: updating a resident table field after load in QlikView

updating a resident table field after load

Mon, 06 Oct 2014 20:02:33 GMT

Hi,

I have a sql load statement as follows:

myStore;

SQL SELECT

storeid as [original storeid],

from ....;

I then load a lookup table as follows:

lookup:

LOAD

storeid as [original storeid],

storeid_to_use as StoreID

FROM ....

After these loads I would like to add a key field to myStore table that is a concatenation of the StoreID and x fields:

StoreID+x as key_store_x

How would I do this?

Re: updating a resident table field after load

Mon, 06 Oct 2014 20:06:13 GMT

do not create lookup table..

left join myStore

Load

storeid as [original storeid],

storeid_to_use as StoreID

FROM ....

join myStore

Load

StoreID & x

resident myStore ;

Re: updating a resident table field after load

JonnyPoole — Mon, 06 Oct 2014 20:09:48 GMT

You could add this on the bottom.

--------------------

left join (myStore)

LOAD

[original storeid],

[StoreID]

resident lookup;

myStore2:

LOAD

StoreID & x as [key_store_x]

resident myStore;

drop table myStore;

----------

Re: updating a resident table field after load

maxgro — Mon, 06 Oct 2014 20:25:55 GMT

lookup:

mapping LOAD

storeid ,

storeid_to_use

FROM ....

myStore:

load

applymap('lookup', [original storeid]) & x as key_store_x;

SQL SELECT

storeid as [original storeid],

from ....;

Re: updating a resident table field after load

Mon, 06 Oct 2014 20:53:01 GMT

Hi,

Thanks for your suggestion; however, the solution does not seem to produce the desired result. Additionally, the side effect of left join myStore is that it causes synthetic keys to be produced in my data model as it includs the 'StoreID' in the fact table. The lookup table load can not be avoided as it contains additional fields used by the app expressions. My current simplified model for these two tables looks like this:

lookup_table: fact_table:

[original storeid] [original storeid]

StoreID x

lookup1 y

lookup2 z

lookupn

After the above two tables are loaded and are in the model, I want to add a key field to my fact_table:

StoreID & x

Re: updating a resident table field after load

Mon, 06 Oct 2014 23:19:20 GMT

Your suggestion worked great.