Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
userid128223
Creator
Creator

Comma seperated field evaluation

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

15 Replies
tresesco
MVP
MVP

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
Creator
Creator
Author

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

rnt
Employee
Employee

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

Great dreamer's dreams never fulfilled, they are always transcended.
geminihzh
Specialist
Specialist

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
Luminary Alumni
Luminary Alumni

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

View solution in original post