Comma seperated field evaluation

[userid128223]

I have a field like below.

ProductValue

23,5,34,55,90,106,200

9,8,88,92,7,55

22,44,99,82

Question1: How can i find max value for Product Value Field.

Ans:

ProductValue                        Max   

23,5,34,55,90,106,200          200

9,8,88,92,7,55                        92

22,44,99,82                              99

Question2: How can i find counts for field that is greater then 30.

Ans:

ProductValue                        Count > 30   

23,5,34,55,90,106,200        5

9,8,88,92,7,55                        3

22,44,99,82                            3

[tresesco]

What is your expected output for:

ProductValue                       Output 

23,5,34,55,90,106,200           ?

9,8,88,92,7,55                        ?

22,44,99,82                            ?

[userid128223]

Indicator

Over 100    -- Selected

Under 100

Results:

Product Value

23,5,34,55,90,106,200

Indicator

Over 100

Under 100  -- Selected

Results:

9,8,88,92,7,55

22,44,99,82

[Ralf-Narfeldt]

Max(SingleValue)>100 As Over100

Add this to the Max: load from above

Max:

LOAD

  RecId,

  Max(SingleValue) as MaxValue,

  sum(If(SingleValue>30,1)) as Over30Count,

  Max(SingleValue)>100 As Over100

Group By RecId

;

[PrashantSangle]

Hi,

try below script

test:

LOAD RowNo() as row,ProductValue inline [

ProductValue

"23,5,34,55,90,106,200"

"9,8,88,92,7,55"

"22,44,99,82"

];

t1:

LOAD row,if(SubField(ProductValue,',')<100,'Less than 100','higher') as t Resident test;

t2:

Load row, Concat(distinct t,',') as t2 Resident t1 group by row;

test_final:

LOAD row,if(wildmatch(t2,'*higher*'),'higher','Less than 100') as testfield Resident t2;

Right join

Load * Resident test;

DROP Table t2,test,t1;

It is working for me and I think you can optimize it.

Regards

[Anonymous]

Hi, 

Then, add another LOAD based on Rob's solution, also works.

Max:

LOAD

  if (Over100Count>0, 'Over 100',

  if (Over30Count>0, '30 -100', 'Under 30')) as Indicator,

  *

;

LOAD

  RecId,

  Max(SingleValue) as MaxValue,

  sum(If(SingleValue>30,1)) as Over30Count,

  sum(If(SingleValue>100,1)) as Over100Count

Group By RecId

;

LOAD

  RecId,

  SubField(ProductValue,',') as SingleValue

Resident Data

;

Zhihong

[rwunderlich · Accepted solution]

A simple way would be to add another LOAD on top. Example attached.

Max:

LOAD

  *,

  if(MaxValue > 100, 'Over 100', 'Under 100') as Indicator

;

LOAD

  RecId,

  Max(SingleValue) as MaxValue,

  sum(If(SingleValue>30,1)) as Over30Count

Group By RecId

;

LOAD

  RecId,

  SubField(ProductValue,',') as SingleValue

Resident Data

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com