Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!!!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

5 Replies
Miguel_Angel_Baeyens

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
Author

Perfect it works fine!

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

johnw
Champion III
Champion III

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
Author

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!!!!

johnw
Champion III
Champion III

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.