How to read values from a table column from any row into a variable

[#1]

I have a table like this:

Product	Country	Percent Sales
ProductA	Brazil	60%
ProductA	Canada	30%
ProductA	Australia	10%
ProductB	US	50%
ProductB	Canada	35%
ProductB	Israel	5%
ProductB	India	10%
ProductC	Brazil	65%
ProductC	US	30%
ProductC	UK	5%

I want to capture the values in the cell, when user selects any product. For example I want to capture Percent sales for each country as shown below:

SET	vProductABrazil=	60%
SET	vProductACanada=	30%
SET	vProductAAustralia=	10%

I am aware that we can use PEEK function. But in order to use PEEK()  we need to specify the row number. I would not be able to do that in this case. Is there another way where I can specify Product and Country in a WHERE Clause in the script and capture the corresponding Percent Sales in a variable?

Thanks in advance.

KS

[ahaahaaha]

Hi,

Two questions.

1) If the choice is made by the user, then the data should already be loaded (it is necessary from something to choose). Therefore, the idea based on the user's choice to do later something in the script is somewhat doubtful. This contradicts logic Qlik.

2) What is the need to create so many variables? I think this will a priori slow down the whole system. Why can not the data in the analysis be used directly from the tables?

Regards,

Andrey

[ali_hijazi]

you can also use lookup function

check the help for detailed information on how to use this function

[jonathandienst · Accepted solution]

Something like this will loop through the table and dynamically create the variables:

Work:

LOAD Product, Country, [Percent Sales]

Resident Data;

For i = 0 To NoOfRows('Work') - 1

  Let vPr = Peek('Product', i, Work');

  Let vCo = Peek('Country', i, Work');

  Let vPs = Peek('Percent Sales', i, Work');

  Set v$(vPr)$(vCo) = $(vPs);

Next

[#5]

That is good suggestion. I will try to implement it without using too many variables. Thanks Andrey.

[#6]

Thank you very much Jonathan. This is very helpful. I will give this a try.