How can we create a new field based on the values of some other fields??

[#1]

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???

[Anonymous]

cud you please explain a little more how you decide the Type i.e. good, bad

[maxgro]

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;

[#4]

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.

[#5]

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.

[sunny_talwar]

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

[#7]

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

[sunny_talwar]

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

[#9]

$                                      -	$                   -	$                               -	$                               -	$            (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?

[#10]

And also there are negative values