Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

String variables in script

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 !

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

Add quotes around $() in Load as below

LOAD

'$(vVille)' & id as idClient;

View solution in original post

9 Replies
Anonymous
Not applicable
Author

Why not use the qualify statement ?

Anonymous
Not applicable
Author

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

then the script will interpret it

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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

anbu1984
Master III
Master III

Add quotes around $() in Load as below

LOAD

'$(vVille)' & id as idClient;

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

Thank you everyone !

Have a good day