Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Defining a new data to existing field

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!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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


View solution in original post

6 Replies
maxgro
MVP
MVP

can you post some data and/or script?

Not applicable
Author

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

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;

Not applicable
Author

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

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


View solution in original post

Not applicable
Author

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