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

[sunny_talwar]

Ya this seems like a formatting issue then. If that is fixed, the above script will give you the results you are looking for. Can you fix the format in the database itself?

Best,

S

[sunny_talwar]

For handling the negative value issue, I would use the fabs function. Use the below script:

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(fabs([Product 1 Sales]), fabs([Product 2 Sales]), fabs([Product 3 Sales]), fabs([Product 4 Sales])) = RangeMax(fabs([Product 1 Sales])), 'Good',

  If(Rangemax(fabs([Product 1 Sales]), fabs([Product 2 Sales]), fabs([Product 3 Sales]), fabs([Product 4 Sales])) = RangeMax(fabs([Product 2 Sales])), 'Bad',

  If(Rangemax(fabs([Product 1 Sales]), fabs([Product 2 Sales]), fabs([Product 3 Sales]), fabs([Product 4 Sales])) = RangeMax(fabs([Product 3 Sales])), 'Average',

  If(Rangemax(fabs([Product 1 Sales]), fabs([Product 2 Sales]), fabs([Product 3 Sales]), fabs([Product 4 Sales])) = RangeMax(fabs([Product 4 Sales])), 'Better')))) as Type

Resident Table;

DROP Table Table;

Best,

S

[#4]

looks like its giving me the desired results!!!!

Thanks a ton!!!!

[sunny_talwar]

Not a problem. Hope you have a good one.

Best,

S

[timanshu]

Can You Attach excel/qvw/screenshot of original data To give more clear picture of issue??