topic Script little help in QlikView

Script little help

Mon, 10 Jan 2011 17:49:30 GMT

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

Script little help

Miguel_Angel_Baeyens — Mon, 10 Jan 2011 18:21:32 GMT

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

Script little help

Mon, 10 Jan 2011 18:35:09 GMT

Perfect it works fine!

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

Script little help

johnw — Mon, 10 Jan 2011 22:43:46 GMT

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;

Script little help

Tue, 11 Jan 2011 08:57:33 GMT

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

Script little help

johnw — Tue, 11 Jan 2011 18:40:17 GMT

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.