topic Re: Qlikview will not read field and create a variable in QlikView

Qlikview will not read field and create a variable

Thu, 18 Aug 2016 08:22:26 GMT

I have this weird problem. I got an excel sheet that got 3 columns

Ticker	Exchange	TickerAndExchange
TEL	.OL	TEL.OL
YAR	.OL	YAR.OL
NHY	.OL	NHY.OL
NOFI	.OL	NOFI.OL

I have created three variables so that I can use these values in creating a URL string:

for i=1 to NoOfRows('TickerList')

LET vNameOfStock = FieldValue('Ticker', $(i));

LET vStockAndExchange = FieldValue('StockAndExchange', $(i));

LET vNameOfExchange = FieldValue('Exchange', $(i));

LET vFullPathStock = '..\StockMarketAnalysis\QVD\' & '$(vNameOfStock)' & '.qvd';

LET vFileExsist = if(FileSize('$(vFullPathStock)')>0,-1,0);

if $(vFileExsist) then

Call Incremental(vNameOfStock, vNameOfExchange);

Else

Call NonIncremental(vNameOfStock, vNameOfExchange);

End if;

Next i;

Somehow the variable vNameOfExchange will only catch the first rows value, and the rest is NULL. I have tried to remove the dot, I have changed the order of the variables, I have tried to empty the variables using LET vNameOfExchange = NULL();

Anyone who knows why this is happening?

Best regards

Espen

Re: Qlikview will not read field and create a variable

sunny_talwar — Thu, 18 Aug 2016 08:38:56 GMT

FieldValue function only look at the distinct combination of values within a field. Since all 4 rows contain the same value .OL. it picks it only the first time. Do you have more Exchange or is it always .OL?

Re: Qlikview will not read field and create a variable

Thu, 18 Aug 2016 08:41:12 GMT

Aha, i see. Well, i could have different values, at the moment i dont, but in future yes. Should I rather use some sort of PEEK()?

Best regards
Espen

Re: Qlikview will not read field and create a variable

sunny_talwar — Thu, 18 Aug 2016 08:42:30 GMT

Yes, I think Peek() would be a better way to do this.

Re: Qlikview will not read field and create a variable

Thu, 18 Aug 2016 08:44:47 GMT

Ok, thanks a lot - I would never figured that out on my own.

Espen

Re: Qlikview will not read field and create a variable

sunny_talwar — Thu, 18 Aug 2016 08:50:35 GMT

Or may be try this approach:

TickerList:

LOAD *,

Exchange&'|'&RecNo() as New_Exchange;

LOAD Ticker,

Exchange,

TickerAndExchange as StockAndExchange

FROM

[https://community.qlik.com/thread/229531]

(html, codepage is 1252, embedded labels, table is @1);

for i=1 to NoOfRows('TickerList')

LET vNameOfStock = FieldValue('Ticker', $(i));

LET vStockAndExchange = FieldValue('StockAndExchange', $(i));

LET vNameOfExchange = SubField(FieldValue('New_Exchange', $(i)), '|', 1);

Re: Qlikview will not read field and create a variable

sunny_talwar — Thu, 18 Aug 2016 08:52:39 GMT

Attaching the incomplete sample, but it seems that all Exchanges get picked

Re: Qlikview will not read field and create a variable

Thu, 18 Aug 2016 08:53:30 GMT

Thanks.

Yes I was thining about some workaround, but I wanted to figure out what was wrong, and I tested with PEEK() now, it works perfect.