Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

reinholdgraf
Contributor

Data-Manipulation when loading data

I like to change some data when loading it.

e.g.

Divisions come in as numbers (100,200,300).

When loading, I want to change let' say 100 to 100 - Test1, 200 to 200 - Test2, etc.

How can I do this ?

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Data-Manipulation when loading data

You can easily do this with ApplyMap.

First, you must store your division names together with division numbers. I have done this in an inline in the script, but you can use any source - a DB or an Excel sheet. Then all you need to do is to construct what you want with ApplyMap():

NameMap:
Mapping Load * Inline
[DivisionNo, DivisionText
100, Poland
200, UK
300, Austria
400, Switzerland-Sub1]
;

Data:
Load *,
DivisionNo & ' - ' & ApplyMap('NameMap',DivisionNo,'Unknown') as Division
From Source (...)

HIC

7 Replies
igdrazil
Contributor III

Re: Data-Manipulation when loading data

Hi Reinhold,

try something like this:

temp:

LOAD * Inline [

Nr

100

200

300];

LOAD Nr & ' - Test' & rowno() as Number resident temp;

Test.JPG

Regards

Chris

reinholdgraf
Contributor

Re: Data-Manipulation when loading data

ok.

But instead of Test, I will use our internal divison-names.

e.g. Poland, UK, Austria, Switzerland-Sub1, etc.

Something like an allocation-table

igdrazil
Contributor III

Re: Data-Manipulation when loading data

Are they in same order as the numbers? then u can just add these two to 1 like:

LOAD number & ' - ' & country as Division;

Regards

Chris

reinholdgraf
Contributor

Re: Data-Manipulation when loading data

no. the names are in some cases the country, in other cases not. (historical reasons).

igdrazil
Contributor III

Re: Data-Manipulation when loading data

I guess u have to put them manually together, when they are in 2 different columns and have to shared order.

At least i have no more clue how to do this, sorry.

Regards

Chris

Employee
Employee

Re: Data-Manipulation when loading data

You can easily do this with ApplyMap.

First, you must store your division names together with division numbers. I have done this in an inline in the script, but you can use any source - a DB or an Excel sheet. Then all you need to do is to construct what you want with ApplyMap():

NameMap:
Mapping Load * Inline
[DivisionNo, DivisionText
100, Poland
200, UK
300, Austria
400, Switzerland-Sub1]
;

Data:
Load *,
DivisionNo & ' - ' & ApplyMap('NameMap',DivisionNo,'Unknown') as Division
From Source (...)

HIC

reinholdgraf
Contributor

Re: Data-Manipulation when loading data

That's it.

Many Thanks

Community Browser