Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I try to do something, but i don't know if it's possible.
I need your help.
My exemple is very simplistic, but in reality, my datas are much complexe and i must load them in the exemple order.
First table : TABLE1
Country | City |
---|---|
USA | Miami |
FRANCE | Paris |
USA | Dallas |
SPAIN | Madrid |
Second table join to the first table with concatenate (TABLE1)
TABLE2
city | language |
---|---|
Lyon | French |
New York | English |
Result :
TABLE1
Country | City | Language |
---|---|---|
USA | Miami | |
FRANCE | Paris | |
USA | Dallas | |
SPAIN | Madrid | |
Lyon | French | |
New York | English |
The third table to complete partial column Country
TABLE 3
Country | City |
---|---|
FRANCE | Lyon |
USA | New York |
USA | Atlanta |
USA | Los Angeles |
Expected résult with a left join
TABLE1
Country | City | Language |
---|---|---|
USA | Miami | |
FRANCE | Paris | |
USA | Dallas | |
SPAIN | Madrid | |
FRANCE | Lyon | French |
USA | New York | English |
In sql, i should do something with where clause : where table1.country is null or in join statement table1.country=table3.country and table1.country is null.
How to do something similar in Qlikview ?
I have an issue by adding a new field in left join, drop the older and rename new field.
Left join (Table1)
city,
Country as Country_temp
From Table3;
Drop field Country from Table1;
Rename Country_temp to Country;
If you have another solution !?!
May be using ApplyMap with Mapping load
TABLE3:
Mapping
LOAD City,
Country;
LOAD * INLINE [
Country, City
FRANCE, Lyon
USA, New York
USA, Atlanta
USA, Los Angeles
];
TABLE1:
LOAD * INLINE [
Country, City
USA, Miami
FRANCE, Paris
USA, Dallas
SPAIN, Madrid
];
Concatenate(TABLE1)
LOAD * INLINE [
City, Language
Lyon, French
New York, English
];
FinalTable:
NoConcatenate
LOAD If(Len(Trim(Country)) = 0, ApplyMap('TABLE3', City), Country) as Country,
City,
Language
Resident TABLE1;
DROP Table TABLE1;
Thanks Sunny
It's a possible solution for my problem but, as i have a lot of data, reload all data several times will slow down too much my global load.
I am not sure what extra load are you seeing above? I mean I have a final load, but you mentioned several times? I am not sure what you mean when you say reload all data several times?
sorry, i was not clear.
This manipulation comes back several times in my script for different value.
I must proceed with intermediate steps to complete my data, so reload several times the totality of the data it's not possible.
Unless you show your script of what you have, I am having a hard time picturing what you have and why you end up with several manipulative resident loads. I really want to help, but I won't be without more information