updating a resident table field after load

[#1]

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?

[#2]

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]

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 · Accepted solution]

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 ....;

[#5]

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

[#6]

Your suggestion worked great.