Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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
Perfect it works fine!
If in the future I have to have multiple IF? Can I create a conversion table or something?
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;
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!!!!
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.