topic String variables in script in QlikView

String variables in script

Fri, 16 May 2014 10:28:31 GMT

Hello everyone,

I extract data from 5 databases which have excatly the same structure (same tables, same name of columns). Each database contains the data for one city.

I want to use all these data in one only table. The problem is that, I have in each table, idCustomer=1 whereas it doesn't stand for the same customer...

So what I've done so far is that I've created a new column 'City' when I import the data and then I concatenate the first letter of the city in front of every ID. So I have for example, P01, L01 and so on...

These is a piece of code :

LOAD

'Paris' AS Ville,

'P'&id AS id_client

FROM TableCity

And I do this for each city...

It has worked so far but the thing is that in the future, there will be more cities... and besides, I dont like the fact that I have to write the 'P' in front of the names of each columns..

I would like to use a variable that contains the 'P' and something like this :

LET vVille='P';

LOAD

$(vVille) & id as idClient;

SQL SELECT * FROM TableCity

But it doesn't work because the string variable doesnt "remember" the ' ' in the LOAD...

Have you got an idea of how I could do this ?

Or if you have a more consistent solution to solve my problem, i would also glad to read it !

Thank you very much and have a good day !

Re: String variables in script

Anonymous — Fri, 16 May 2014 10:56:36 GMT

Why not use the qualify statement ?

Re: String variables in script

Anonymous — Fri, 16 May 2014 10:58:06 GMT

and if you Chr(39) instead of the apostrophe.

then the script will interpret it

Re: String variables in script

Fri, 16 May 2014 11:00:00 GMT

Thank you eruditio, actually the example was wrong because all the tables have the same name.. I've corrected ot.

Re: String variables in script

Anonymous — Fri, 16 May 2014 11:02:58 GMT

another way is '$(vVille)' & id as idClient;

Re: String variables in script

Fri, 16 May 2014 11:03:53 GMT

Thank you, I will try it.

But what is the syntax ? Where do I put the Chr(39) ?

LET vVille=' P';

Thank you for your help

Re: String variables in script

anbu1984 — Fri, 16 May 2014 11:04:35 GMT

Add quotes around $() in Load as below

LOAD

'$(vVille)' & id as idClient;

Re: String variables in script

jonathandienst — Fri, 16 May 2014 11:04:52 GMT

Remember that a variable expansion using $() is applied before the statement is interpreted. So just use:

LOAD '$(vVille)' & id as idClient;

The quotes in the Set statement are assumed to be delimiters and are not included in the string.

HTH

Jonathan

Re: String variables in script

Joaquin_Lazaro — Fri, 16 May 2014 11:06:00 GMT

HI:

First option: Hardcode to your origin data

LOAD *

'Origin1' & yourID as OriginID

from ...

Second option: A variable with your origin data

vOrigin = 'Origin1'

LOAD *

$v(Origin) & yourID as OriginID

from ...

Hope it helps you

Joaquín

Re: String variables in script

Fri, 16 May 2014 11:07:50 GMT

Thank you everyone !

Have a good day