topic Re: Adding a new column with Field value from same table in QlikView

Adding a new column with Field value from same table

quiquehm — Sat, 16 Nov 2024 20:39:09 GMT

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 !!

Re: Adding a new column with Field value from same table

awaisqureshi — Mon, 17 Jun 2019 16:31:57 GMT

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;

Re: Adding a new column with Field value from same table

quiquehm — Tue, 18 Jun 2019 13:41:01 GMT

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