topic Re: How can we create a new field based on the values of some other fields?? in QlikView

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

Thu, 19 Feb 2015 22:44:40 GMT

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

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

Anonymous — Thu, 19 Feb 2015 22:50:30 GMT

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

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

maxgro — Thu, 19 Feb 2015 22:58:06 GMT

How to choose Good, Bad, ....?

From your post Good is the max but Bad isn't the min.

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 ];

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;

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

Fri, 20 Feb 2015 03:07:07 GMT

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.

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

Fri, 20 Feb 2015 03:08:04 GMT

Thanks Rajan!!!

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

sunny_talwar — Fri, 20 Feb 2015 03:23:48 GMT

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,

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

Fri, 20 Feb 2015 04:26:57 GMT

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

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

sunny_talwar — Fri, 20 Feb 2015 04:29:26 GMT

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,

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

Fri, 20 Feb 2015 04:33:06 GMT

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

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

Fri, 20 Feb 2015 04:34:47 GMT

And also there are negative values

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

sunny_talwar — Fri, 20 Feb 2015 04:36:15 GMT

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,

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

sunny_talwar — Fri, 20 Feb 2015 04:39:39 GMT

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,

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

Fri, 20 Feb 2015 04:49:04 GMT

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

Thanks a ton!!!!

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

sunny_talwar — Fri, 20 Feb 2015 04:50:23 GMT

Not a problem. Hope you have a good one.

Best,

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

timanshu — Fri, 20 Feb 2015 04:57:09 GMT

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