Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jonah-vq
New Contributor III

Correct/replace data in load script via separate Excel

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)                

PeriodAccountDimension%PeriodAccount
20170398745BCE201703|98745
20170312365DHE201703|12365
20170298745ABC201702|98745
20170212365DHE201702|12365
20170198745ABC201701|98745
20170112365DHE201701|12365

Correction file

Dimension corrected%PeriodAccount
BCE201702|98745
BCE201701|98745

Corrected source data

PeriodAccountDimension%PeriodAccount
20170398745BCE201703|98745
20170312365DHE201703|12365
20170298745BCE201702|98745
20170212365DHE201702|12365
20170198745BCE201701|98745
20170112365DHE201701|12365

Thanks in advance!

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

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

use Applymap()

quick applymap example

2 Replies
vinieme12
Esteemed Contributor II

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

use Applymap()

quick applymap example

jonah-vq
New Contributor III

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

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.

Community Browser