Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Script little help

Hello all,

I'm a newbie so please forgive me for this question if it is too easy.

I have already made the my SQL script and the pivot tables, the charts everything. The only problem I have and I do not want to change the SQL script is the following:

My lines have some values like this.

peter, student, patras
tom, student, athens
john, worker, mykonos

I want every time that I get the value 'athens' to transform it to 'central'. How can I do this in the script?

Thanks!!!

Tags (3)
1 Solution

Accepted Solutions

Script little help

Hello,

Do something like the following in the script (it will save you time in debugging charts and will perform better):

Person:LOAD Name, Position, If(City = 'Athens', 'Central', City) AS City;SQL SELECT Name, Position, CityFROM database.table;


Hope this helps

5 Replies

Script little help

Hello,

Do something like the following in the script (it will save you time in debugging charts and will perform better):

Person:LOAD Name, Position, If(City = 'Athens', 'Central', City) AS City;SQL SELECT Name, Position, CityFROM database.table;


Hope this helps

Not applicable

Script little help

Perfect it works fine!

If in the future I have to have multiple IF? Can I create a conversion table or something?

MVP
MVP

Script little help

If it's only a few values, I'd probably do a nested if. When more than a few values, consider a mapping table:

MyMap:
MAPPING LOAD * INLINE [
Change This, To This
Athens, Central
NYC, New York City
etc.
];

Person:
LOAD Name,
Position,
applymap('MyMap',City,City) AS City;
SQL SELECT Name, Position, City
FROM database.table;

Not applicable

Script little help

It seems better but what about the cities I want to remain unchanged? I only get values for the cities I put in the map.

Moreover is it possible to put something like 'Others' for all the cities that I do not want to put one by one in the map?

Thanks!!!!

MVP
MVP

Script little help

If you're only getting cities that you listed in the map, then there's something wrong. The third parameter in the applymap() is the default. So by default, if you don't find the city in the map, the script I gave should use the City field itself. If you wanted to use 'Others' instead, you would put 'Others' in the third parameter in the applymap() instead. Look up applymap in the help text for other examples.

Community Browser