Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
Add quotes around $() in Load as below
LOAD
'$(vVille)' & id as idClient;
Why not use the qualify statement ?
and if you Chr(39) instead of the apostrophe.
then the script will interpret it
Thank you eruditio, actually the example was wrong because all the tables have the same name.. I've corrected ot.
another way is '$(vVille)' & id as idClient;
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
Add quotes around $() in Load as below
LOAD
'$(vVille)' & id as idClient;
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
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
Thank you everyone !
Have a good day