Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
alex_stone
Creator
Creator

How to get cell value from straight table?

Currently, I have one straight table like below:

User IDUser NameSales NameYTD Revenue
1009xxSales790000
1002xxSales480000
1003xxSales570000
1008xxSales1160000
2001xxSales350000

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?

1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

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.

View solution in original post

7 Replies
Anil_Babu_Samineni

What you want to store in Variable?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable

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.

Sergey_Shuklin
Specialist
Specialist

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!

alex_stone
Creator
Creator
Author

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.

Sergey_Shuklin
Specialist
Specialist

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.

alex_stone
Creator
Creator
Author

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.

Sergey_Shuklin
Specialist
Specialist

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! 😃