Qlikview will not read field and create a variable

[#1]

Hi

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

[sunny_talwar · Accepted solution]

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?

[#3]

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

[sunny_talwar]

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

[#5]

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

BR

Espen

[sunny_talwar]

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);

[sunny_talwar]

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

[#8]

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.