topic Re: Defining a new data to existing field in QlikView

Defining a new data to existing field

Thu, 04 Sep 2014 11:16:49 GMT

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!

Re: Defining a new data to existing field

maxgro — Thu, 04 Sep 2014 11:31:08 GMT

can you post some data and/or script?

Re: Defining a new data to existing field

Thu, 04 Sep 2014 11:34:14 GMT

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;

Re: Defining a new data to existing field

maxgro — Thu, 04 Sep 2014 11:43:37 GMT

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;

Re: Defining a new data to existing field

Thu, 04 Sep 2014 12:03:27 GMT

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?

Re: Defining a new data to existing field

maxgro — Thu, 04 Sep 2014 12:32:22 GMT

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

Re: Defining a new data to existing field

Thu, 04 Sep 2014 12:55:31 GMT

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