Getting the top product based on amount

[chiukim]

What is the virtual column formula gonna use in table 1, if i want to get  the value of the [product] per customer from table 2 based on amount sold.

I want to get the top 1, 2 and 3 products based on amount. 

[Suvrutt_Gurjar]

Please try below. Please create a slice called say "Top3" in the Table 2 with an expression something like

IN([sales_id],  TOP(ORDERBY(FILTER("Table 2", [customer_id]=[_THISROW].[customer_id]), [amount], TRUE), 3))

In the Table 1, create a reverse reference column VC with an expression something like 

REF_ROWS("Top3" , "customer_id")

Please enter referenced table name as "Top3" slice in this reverser reference column.

[chiukim]

Thanks for the input.

But I need a total 3 VCs . one for top 1 and so on. Each VC should have a 1 output not a list. 

[Suvrutt_Gurjar]

Thank you for the clearer requirement. Would you want the VC to contain only amount or reference to the Table 2 row ?

[chiukim]

just a value who contains the top 1 in sales per customer based on amount. 

[Suvrutt_Gurjar]

Thank you. 

---

@Suvrutt_Gurjar wrote:

In the Table 1, create a reverse reference column VC with an expression something like 

REF_ROWS("Top3" , "customer_id")

---

Please call the above rev reference column say [Top3]

Then your VC1 called Top1 can have an expression of 

INDEX([Top3][Product],1)

Then your VC2 called Top2 can have an expression of 

INDEX([Top3][Product],2)

Then your VC3 called Top3 can have an expression of 

INDEX([Top3][Product],3)

[chiukim]

I see. Is there any other way in exchange of creating slice 1st? Im dealing with big files.  Im having error(to long to process formula) whenever i use top(orderby() function in a formula. 

[chiukim]

Example in the given data above.
the 1st virtual column should have an answer of grapes. 
the 2nd virtual column should have an answer of oranges. 
is it possible?

[Suvrutt_Gurjar]

Yes, I was about to mention the general warning that when you are dealing with multirow formulas in VCs, these performance issues could arise. One needs to avoid these as much as possible.

Are the two tables referenced? Is the table 1 referenced in table 2?

[chiukim]

They are not referenced. 

[Suvrutt_Gurjar]

Then sorry, I will not be able to suggest an economical expression, when you wish to scan an entire table per customer ID to select certain values.

If those were referenced, we could have used system generated reverse reference column to get those top 3 values,

[chiukim]

How can i referenced table 1 to table 2? using max(select()?

[Suvrutt_Gurjar]

In general , your customer_id in Table 2 could be a reference column referencing the Table 1 if customer_if is the key of Table 1.

However , please do any such changes at your own risk as we are unaware of your other app configuration and impact these changes may have elsewhere in your app. 

My one more request will be to state the requirement in more detail to start with so that community could help with a better solution.

[chiukim]

yes customer_id is the key on my table 1. 

[chiukim]

Can you help me how to get the key id of table 2 who has the biggest amount?

[Suvrutt_Gurjar]

I have already described the approach and in your screenshot also you have a reverse reference list from another table. You need to use the same approach to reference Table 2.

[chiukim]

their relationship is table 1 (one) to table 2(many)