Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

I have a table like this:

   

ProductCountryPercent Sales
ProductABrazil60%
ProductACanada30%
ProductAAustralia10%
ProductBUS50%
ProductBCanada35%
ProductBIsrael5%
ProductBIndia10%
ProductCBrazil65%
ProductCUS30%
ProductCUK5%

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

1 Solution

Accepted Solutions
MVP
MVP

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

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 Replies
ahaahaaha
Honored Contributor

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

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
Honored Contributor

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

you can also use lookup function

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

MVP
MVP

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

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

Not applicable

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

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

Not applicable

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

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

Community Browser