topic Re: How to get cell value from straight table? in QlikView

How to get cell value from straight table?

alex_stone — Thu, 15 Jun 2017 04:31:22 GMT

Currently, I have one straight table like below:

User ID	User Name	Sales Name	YTD Revenue
1009	xx	Sales7	90000
1002	xx	Sales4	80000
1003	xx	Sales5	70000
1008	xx	Sales11	60000
2001	xx	Sales3	50000

I want to try to get the Sales Name one by one , and then set into variable one by one.

Is there any function that we can get cell data from existing straight table? or Others?

Re: How to get cell value from straight table?

Anil_Babu_Samineni — Thu, 15 Jun 2017 05:14:11 GMT

What you want to store in Variable?

Re: How to get cell value from straight table?

Thu, 15 Jun 2017 05:25:18 GMT

Maybe you need to add a column like 'xxID' and set values for ' 1 2 3 4 5 6'

then you can use it to get 'SalesName' one by one with some kind of loop function.

Re: How to get cell value from straight table?

Sergey_Shuklin — Thu, 15 Jun 2017 06:50:42 GMT

Hello, Alex!

Not clear enough what it's mean "one by one". If you want to loop field automatically, searching for some exact value - this is one task. If you want to set values into the variable by hands - this is another task.

In attachement below you can find a solution how to set values manualy (clicking on specified arrows).

Maybe this is what you want to achieve!

Re: How to get cell value from straight table?

alex_stone — Thu, 15 Jun 2017 07:32:43 GMT

Hi Sergey, I want to create 10 pie chart based on above sales name order. And pick first sales name into the set analysis expression for first pie chart, pick second sales name into set analysis expression for second pie chart, ....so on

How to reach this?

Please paste your expression or solution into word. For security problem, we cannot download outside source.

Re: How to get cell value from straight table?

Sergey_Shuklin — Thu, 15 Jun 2017 07:52:36 GMT

Oh, my apologies about only attached file!

So, this is the way:

Use the FirstSortedValue() function's third parameter to indicate what value should be picked:

=FirstSortedValue(

aggr(SalesName,UserID,UserName), //this is what we gonna to pick

-aggr(sum(aggr(YTD_Rev,UserID,UserName)),UserID,UserName), //this is a sort field

1 //this is the ordered number of what we gonna to pick (you can set here a variable)

)

Changing third parameter will let you to pick different values for different charts.

I've let myself to rename you original fields, hope it won't made a difficulties.

Re: How to get cell value from straight table?

alex_stone — Thu, 15 Jun 2017 23:02:10 GMT

Thanks a million! Could you please explain that why add aggr(SalesName,UserID,UserName) on first parameter? Can not we just put SalesName?

-aggr(sum(aggr(YTD_Rev,UserID,UserName)),UserID,UserName)

I also want to query about the highlight part on above statement.

It can work, just want to confirm above questions, thanks again.

Re: How to get cell value from straight table?

Sergey_Shuklin — Fri, 16 Jun 2017 05:38:29 GMT

Hello, Alex!

You defitinely right about that. The expression can be simplified to:

=FirstSortedValue(

SalesName, //this is what we gonna to pick

-aggr(sum(YTD_Rev),UserID,UserName), //this is a sort field

1 //this is the ordered number of what we gonna to pick (you can set here a variable)

)

On my simply inline generated data it works, but I'm not sure about result on some expanded data. I faced cases when it's necessary to use aggr() as much for the first parameter as for the second one.

Anyway, glad to help! 😃