Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
you can also use lookup function
check the help for detailed information on how to use this function
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
That is good suggestion. I will try to implement it without using too many variables. Thanks Andrey.
Thank you very much Jonathan. This is very helpful. I will give this a try.