Qlik Community

App Development

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jonah-vq
Contributor III
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
Champion III
Champion III

use Applymap()

quick applymap example

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
vinieme12
Champion III
Champion III

use Applymap()

quick applymap example

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jonah-vq
Contributor III
Contributor III
Author

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.