Correct/replace data in load script via separate Excel

Anonymous — Fri, 31 Mar 2017 08:51:28 GMT

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!

Re: Correct/replace data in load script via separate Excel

vinieme12 — Fri, 31 Mar 2017 09:52:17 GMT

use Applymap()

quick applymap example

Re: Correct/replace data in load script via separate Excel

Anonymous — Mon, 03 Apr 2017 10:07:05 GMT

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.

topic Re: Correct/replace data in load script via separate Excel in App Development

Correct/replace data in load script via separate Excel

Re: Correct/replace data in load script via separate Excel

Re: Correct/replace data in load script via separate Excel