Is Pivoting the right node?

[kowisoft]

Hi there,

I have the situation, that I want to determine what is the most sold product to a customer.

Imagine this table:

For each customer you could see in which (product) column the most products were sold.

I need to now to have the product in the rightmost column, e. g. Prod 1 for customer A, Prod 2 for customer B, Prod 3 for Customer C etc etc

I wonder what node (or what combination of nodes) I would need to make this work?

Thank you in advance!

[dnaki]

Hi @kowisoft

Here’s one solution using the Math Formula and Column Expressions nodes:

Math Formula is used to identify the column index with the most sales, and is configured like this:

The Column Expressions node is used to convert the previously identified column index into its corresponding name: (the columnNames() expression returns an array of column names, and the index of the desired column name is accessed by specifying it in square brackets).

Hope this helps
Don

[ipazin]

Hi there!

Not sure Pivoting is the right node for this. Not sure how to make it with it. Why not just use max_in_args function from Math Formula node?

Br,
Ivan

[kowisoft]

thank you @dnaki and @ipazin for helping me out.

somehow the MathFormula node has problems parsing this.

When I use colMax I always get the error “Can’t parse the column name of constant function colmax”

When I use max_in_args I can only go through 3 columns, but my original table has 18 columns to be checked. I really cannot spot the problem here…

Thank you again

[ipazin]

Hi!

If I got it right when you use function colMax you shouldn’t have space after column name which might be an undesired behavior. Maybe you have such case.

Regarding function max_in_args have you tried it? It works with more than 3 columns for me.

Br,
Ivan

[kowisoft]

Hi @ipazin

that was the problem. One additional thing I have recognized was that I have missing values in several cells.

To solve this I replaced them using the Missing Value Node and then followed the approach of @dnaki which now works, it returns the product name where the customer made most revenue.

Thank you both VERY much!