Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can someone help me to resolve this issue?
I have four columns Say
Product 1_Sales | Product 2_Sales | Product 3_Sales | Product 4_Sales |
1001 | 0 | 0 | 0 |
0 | 456 | 0 | 0 |
0 | 0 | 123 | 0 |
0 | 0 | 0 | 345 |
Now the challenge is to create a new column field called Type with a Text,which is based on the <>0 Value in the first 4 columns.
Product 1_Sales | Product 2_Sales | Product 3_Sales | Product 4_Sales | Type |
1001 | 0 | 0 | 0 | Good |
0 | 456 | 0 | 0 | Bad |
0 | 0 | 123 | 0 | Average |
0 | 0 | 0 | 345 | Better |
Please share your solutions???
cud you please explain a little more how you decide the Type i.e. good, bad
How to choose Good, Bad, ....?
From your post Good is the max but Bad isn't the min.
a:
load rowno() as id, * inline [
Product1_Sales ,Product2_Sales ,Product3_Sales, Product4_Sales
1001, 0, 0, 0
0, 456 ,0, 0
0 ,0 ,123, 0
0 ,0, 0, 345 ];
b:
load id, rangesum(Product1_Sales ,Product2_Sales ,Product3_Sales, Product4_Sales) as AllProduct_Sales
Resident a;
left join (a)
load id, pick(match(rowno(), 1, 2, 3, 4), 'Good', 'Better', 'Average', 'Bad') as Type
Resident b
order by AllProduct_Sales desc;
DROP Table b;
Thanks Massimo!!!
My concern was,say I have 3k rows in my data.. Doesn't it requires the for loop to create the 'Text' ...plz dnt consider the literal meaning,the logic is,if the cell value of the column Product1_Sales <>0,then the corresponding cell in the column Type should populate the text 'Good' and if the cell value of the column Product2_Sales<>0,the corresponding cell in the Type column in the same row should populate the text 'Better',same applies to other columns....ultimately the column Type should contain the same number of rows as in the other columns with the respective string.
Thanks Rajan!!!
My concern was,say I have 3k rows in my data.. Doesn't it requires the for loop to create the 'Text' ...plz dnt consider the literal meaning,the logic is,if the cell value of the column Product1_Sales <>0,then the corresponding cell in the column Type should populate the text 'Good' and if the cell value of the column Product2_Sales<>0,the corresponding cell in the Type column in the same row should populate the text 'Better',same applies to other columns....ultimately the column Type should contain the same number of rows as in the other columns with the respective string.
Does this work?
Table:
LOAD * INLINE [
Product 1 Sales, Product 2 Sales, Product 3 Sales, Product 4 Sales
1001, 0, 0, 0
0, 456, 0, 0
0, 0, 123, 0
0, 0, 0, 345
];
Table2:
LOAD *,
If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 1 Sales]), 'Good',
If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 2 Sales]), 'Bad',
If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 3 Sales]), 'Average',
If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 4 Sales]), 'Better')))) as Type
Resident Table;
DROP Table Table;
Best,
S
Hey Thanks S,
It worked for some rows,but not all the rows are getting evaluated and the subsequent cell in the Type column is not getting populated
Can you share some information about rows where it is not getting evaluated? What info is in these 4 columns (Product 1 Sales, Product 2 Sales, Product 3 Sales, Product 4 Sales) where the rows are not getting evaluated in the type?
Best,
S
$ - | $ - | $ - | $ - | $ (16,637.33) |
$ - | $ - | $ - | $ - | $ (33,274.65) |
$ 99,823.96 | $ - | $ - | $ - | $ - |
$ 36,046.37 | $ - | $ - | $ - | $ - |
$ - | $ - | $ - | $ - | $ - |
$ - | $ - | $ - | $ - | $ - |
$ - | $ - | $ - | $ (21,627.82) | $ - |
I think its because of the $ Value or the currency format used?
And also there are negative values