Defining a new data to existing field

[#1]

Hello again,

I have a OLEDB database that i retrieve from my database that contains a lot of information about our customers. I would like to sort them by country, state and then postcode.

Since we only have the country and the postcode in the database, I had the idea to create a new field, here state automatically when retrieving the datas from the database.

It would look like this:

SET Liège when the postcode starts with a "4" for Belgium for example, but it should pqy qttention to the amount of numbers in the postcode.. In Belgium we have 4 numbers in the poscode, while in France it is constructed of 5 numbers.

Since I'm a newbe, I don't find a bunch of information and I found nothing about scipting and expressions on the web 😕

Thanks a lot!

[maxgro]

can you post some data and/or script?

[#3]

This is how I select my data, but since it's not possible to write any sql request like "where" or "group by", I don't have anything else yet:

OLEDB CONNECT32 TO [...];

SQL SELECT

  FO_REFERENCE,

  FO_NOM_1,

  FO_REP_CODE,

  FO_CODE_POSTAL,

  FO_VILLE,

  FO_PAYS

FROM FC_REFERENCES;

[maxgro]

add a load before your sql select

qlik will execute the SQL and then the LOAD using the data from the SQL

in the LOAD add your logic, here an example

if you posty your qlik doc (after you load some data from the db and save it) we (community) can use your data to answer

OLEDB CONNECT32 TO [...];

LOAD

     *,

     if(len(FO_CODE_POSTAL)=4,      'Belgium',         'France'     ) as newfield          //calculated fiield

     ;

SQL SELECT

  FO_REFERENCE,

  FO_NOM_1,

  FO_REP_CODE,

  FO_CODE_POSTAL,

  FO_VILLE,

  FO_PAYS

FROM FC_REFERENCES;

[#5]

This could have worked but I misspoke myself.

Here it set a new field for a value with a specific number of character but since there are several postcodes with 4 numbers it doesn't help.
For example, all the postcodes starting with "4" (4000, 4320, 4600 etc) should have the same Province (here Liège) while other postcodes starting with "1" (1000, 1020) etc should have Bruxelles.

I thought of using a simple interval, saying if the postcode beling between 4000 and 4999 set Liège, do you think it is possible?

[maxgro · Accepted solution]

you can use nested if

=

if(left(FO_CODE_POSTAL,4)>=4000 and left(FO_CODE_POSTAL,4)<5000, 'Liège',

if(left(FO_CODE_POSTAL,4)>=5001 and left(FO_CODE_POSTAL,4)<6000, 'othercity',

if(left(FO_CODE_POSTAL,4)>=1001 and left(FO_CODE_POSTAL,4)<2000, 'Bruxelles',

'nocityfound')))

or you can use pick and match/wildmatch

=

pick(wildmatch(left(FO_CODE_POSTAL,1), '1', '2', '3', '4', '*'), 'city1', 'city2', 'city3', 'city4', 'othercity')

or etc...etc....., depends on your data

[#7]

Thanks!!! I used the wildmatch and it works perfectly Thanks a lot