Update table in load script to change missing joins

[Jaeger]

I have to tables in load script of a Qlik Sense app:

Stores:
Load * Inline [
StoreNumber,StoreName
1000,StoreDummy
1170,StoreNorth-Cc
1171,StoreSouth-Cc
];

CostCenters:
Load * Inline [
CcNumber,CcName,StoreNumber
1170,StoreNorth-Cc,1170
1171,StoreSouth-Cc,1171
1100,HR-Cc,1100
1101,IT-Cc,1101
];

The CcNumber usually is equal to the StoreNumber. Is it possible to update the second table in load script and change all the rows that don't have an according StoreNumber in the first table with the StoreNumber 1000 of the StoreDummy?
Result should look like this:
CcNumber,CcName,StoreNumber
1170,StoreNorth-Cc,1170
1171,StoreSouth-Cc,1171
1100,HR-Cc,1000
1101,IT-Cc,1000

[lennart_mo · Accepted solution]

Hi @Jaeger,

in this case I can think of two ways to do this.

The first would be to use 'Exists()' in the second LOAD like this i.e.:

Stores:
Load * Inline [
StoreNumber,StoreName
1000,StoreDummy
1170,StoreNorth-Cc
1171,StoreSouth-Cc
];

CostCenters:
Load 
CcNumber,
CcName,
if(Exists(StoreNumber), StoreNumber, 1000) as StoreNumber
Inline [
CcNumber,CcName,StoreNumber
1170,StoreNorth-Cc,1170
1171,StoreSouth-Cc,1171
1100,HR-Cc,1100
1101,IT-Cc,1101
];

The second option would involve Mapping and could look like this:

Stores:
Load * Inline [
StoreNumber,StoreName
1000,StoreDummy
1170,StoreNorth-Cc
1171,StoreSouth-Cc
];

StoreMap:
Mapping Load
StoreNumber,
StoreNumber as StoreNumber2
RESIDENT Stores;

CostCenters:
Load CcNumber,
CcName,
ApplyMap('StoreMap', StoreNumber, 1000) as StoreNumber
Inline [
CcNumber,CcName,StoreNumber
1170,StoreNorth-Cc,1170
1171,StoreSouth-Cc,1171
1100,HR-Cc,1100
1101,IT-Cc,1101
];

Hope this helps!

[Jaeger]

Great, first option worked for me. Thank you!