Correct/replace data in load script via separate Excel

[Anonymous]

Hello,

I have an existing data model where there is data per month per account. For a certain dimension I would like to correct the source data via a separate Excel file (I could do this in the source data, but I rather have a clear view on the corrections done via this excel).

How can I do this in the load script? Below you'll find a short example of what I want to do.

For account 98745 the dimension was not correct in the source data for Period 201701 & 201702 (replace ABC with BCE), this will be given in the correction file.

Source data (%PeriodAccount is a concattenated field)                

Period	Account	Dimension	%PeriodAccount
201703	98745	BCE	201703|98745
201703	12365	DHE	201703|12365
201702	98745	ABC	201702|98745
201702	12365	DHE	201702|12365
201701	98745	ABC	201701|98745
201701	12365	DHE	201701|12365

Correction file

Dimension corrected	%PeriodAccount
BCE	201702|98745
BCE	201701|98745

Corrected source data

Period	Account	Dimension	%PeriodAccount
201703	98745	BCE	201703|98745
201703	12365	DHE	201703|12365
201702	98745	BCE	201702|98745
201702	12365	DHE	201702|12365
201701	98745	BCE	201701|98745
201701	12365	DHE	201701|12365

Thanks in advance!

[vinieme12 · Accepted solution]

use Applymap()

quick applymap example

[Anonymous]

Thanks, this worked for me by doing :

ApplyMap ('Mapping_table', "%PeriodAccount", "Dimnesion") as "Dimension"

Using the "Dimension" as default value causes the "Dimension" field to keep this cell as-is if there is nothing in the mapping table.