Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello everyone!
Can someone help on a script that can find the minimum value of a row and also return its corresponding header? See the below example.
Thanks in advance.
input:
A | B | C | D | |
John | 3 | 4 | 4 | 8 |
Lisa | 2 | 1 | 8 | 7 |
Alex | 7 | 5 | 1 | 1 |
output:
John | A | 3 |
Lisa | B | 1 |
Alex | C&D | 1 |
Data:
CrossTable(Alpha,Value)
Load * Inline
[
Name A B C D
John 3 4 4 8
Lisa 2 1 8 7
Alex 7 5 1 1
] (delimiter is \t);
Left Join (Data)
Load Name, Min(Value) as MinValue Resident Data Group By Name;
NoConcatenate
Final:
Load Name, Concat(Alpha,',') as Alpha, Value Group By Name, Value;
Load Name, Alpha, Value Resident Data Where Value = MinValue;
Drop Table Data;
What is the logic here?
1) Why John and A is 3 ?
Data:
CrossTable(Alpha,Value)
Load * Inline
[
Name A B C D
John 3 4 4 8
Lisa 2 1 8 7
Alex 7 5 1 1
] (delimiter is \t);
Left Join (Data)
Load Name, Min(Value) as MinValue Resident Data Group By Name;
NoConcatenate
Final:
Load Name, Concat(Alpha,',') as Alpha, Value Group By Name, Value;
Load Name, Alpha, Value Resident Data Where Value = MinValue;
Drop Table Data;
sorry about the confusion. The logic here is that A, B, C and D are the item names and numbers are the quantity each person bought the items. And I would like to know which item each person bought the least.
in row 3, Alex bought C&D the least, which is only once.
Perfect! Thank you so much!