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

updating a resident table field after load

Hi,

I have a sql load statement as follows:

myStore;

SQL SELECT

storeid as [original storeid],

x,

y,

z,

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?

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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],

x,

y,

z,

from ....;

View solution in original post

5 Replies
Not applicable
Author

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 ;

JonnyPoole
Employee
Employee

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;

----------

maxgro
MVP
MVP

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],

x,

y,

z,

from ....;

Not applicable
Author

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

Not applicable
Author

Your suggestion worked great.