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: 
quiquehm
Contributor III
Contributor III

Adding a new column with Field value from same table

I would need to add one extra column to my table of X-rates, simply using one of the values in the table (EUR per USD xrate) and populate this value across all rows in my table. I thought it would be easy , but I can´t find the way to do it. 

Any help is appreciated. Here is the piece of code I am struggling with 

Directory;
LOAD
Country,
CURRENCY,
CODE,
USDperCURR,
CURRperUSD,
if(CODE='EUR',CURRperUSD) as EURperUSD
FROM 201906_MOR.xls (biff, embedded labels, table is Summary$, filters( .......));

I simply need the last calculated field to populate for all rows ...not only for the row where CODE=EUR ( like in attached printscreen ).

Thank you !!

Labels (2)
1 Solution

Accepted Solutions
awaisqureshi
Contributor III
Contributor III

There might be a more efficient method of doing it but this should get you what you're looking for.

 

Directory;
LOAD
Country,
CURRENCY,
CODE,
USDperCURR,
CURRperUSD
FROM 201906_MOR.xls (biff, embedded labels, table is Summary$, filters( .......));

EURperUSD:
load
CURRperUSD as EURperUSD
Resident Directory
where CODE='EUR';

LET vEURperUSD = peek('EURperUSD',0,'EURperUSD');

NoConcatenate
Directory2:
load
*,
$(vEURperUSD) as EURperUSD
resident Directory;

drop tables Directory, EURperUSD;

 

View solution in original post

2 Replies
awaisqureshi
Contributor III
Contributor III

There might be a more efficient method of doing it but this should get you what you're looking for.

 

Directory;
LOAD
Country,
CURRENCY,
CODE,
USDperCURR,
CURRperUSD
FROM 201906_MOR.xls (biff, embedded labels, table is Summary$, filters( .......));

EURperUSD:
load
CURRperUSD as EURperUSD
Resident Directory
where CODE='EUR';

LET vEURperUSD = peek('EURperUSD',0,'EURperUSD');

NoConcatenate
Directory2:
load
*,
$(vEURperUSD) as EURperUSD
resident Directory;

drop tables Directory, EURperUSD;

 

quiquehm
Contributor III
Contributor III
Author

Thanks a lot ! I just tried your script and of course it works.

Only one comment is, and sorry if it caused confusion , that the name of the table is not "Directory", this is just a Qlikview statement that defines the directory where the files being loaded are located .  In my case I think there is no need to add a path to the directory statement, as I am using relative paths in the data load  ( I am not even sure if Directory is needed at all in my case ...I will try without it ).

As you also mentioned, I am curious to see if there could be an easier/faster method to do this ( without using a variable and the peek function ) ....any other ideas out there ? 

Many thanks for your help