Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

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

Can someone help me to resolve this issue?

I have four columns Say

Product 1_SalesProduct 2_SalesProduct 3_SalesProduct 4_Sales
1001000
045600
001230
000345

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_SalesProduct 2_SalesProduct 3_SalesProduct 4_SalesType
1001000Good
045600Bad
001230Average
000345Better

Please share your solutions???

Tags (1)
14 Replies
qlikrajan
Contributor III

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

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

MVP
MVP

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

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

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

1.jpg

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;

Not applicable

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

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.

Not applicable

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

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.

MVP
MVP

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

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

Not applicable

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

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

MVP
MVP

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

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

Not applicable

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

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

Not applicable

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

And also there are negative values

Community Browser