Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
use Applymap()
use Applymap()
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.