Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

userid128223
Contributor

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Comma seperated field evaluation

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

15 Replies
Not applicable

Re: Comma seperated field evaluation

i dont understand realy good of your need...this evaluetion its month?

you can separate this information witch =SubField(Question1,',',1) (1 can change for a number of register dou want)

post a data model print or app for facilite the resolution of your problem

userid128223
Contributor

Re: Comma seperated field evaluation

Hi Silva

I just asked the question in different way. Hopefully it makes it clear.

Basically how do you evaluate a field within itself when they are seperated by comma.

MVP & Luminary
MVP & Luminary

Re: Comma seperated field evaluation

You can do it in script by using the two parameter subfield(). Example attached.

I think there may be a way to do it with a chart expression, I'm looking for an old example.

Data:

LOAD

  RecNo() as RecId,

  ProductValue

INLINE [

ProductValue

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

9,8,88,92,7,55

22,44,99,82

] (delimiter is '|')

;

Max:

LOAD

  RecId,

  Max(SingleValue) as MaxValue,

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

Group By RecId

;

LOAD

  RecId,

  SubField(ProductValue,',') as SingleValue

Resident Data

;

2016-07-21_17-27-38.png

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

userid128223
Contributor

Re: Comma seperated field evaluation

Amazing Thank You.

Follow-up question.

How do i create a indicator field based on ProductValue field for each record.

Indicators:

Over 30

Under 30

mightyqlikers
Contributor III

Re: Comma seperated field evaluation

hi,

PFa.

$@M.

userid128223
Contributor

Re: Comma seperated field evaluation

Hi Sam

Your answer is not what i was looking for. My evaluation of indicator is based on Product Value for each record not all combined.

userid128223
Contributor

Re: Comma seperated field evaluation

My question might be misleading.

I just need 2 indicators.

1) Product Value > 30

2) Product Value < 30

MVP
MVP

Re: Comma seperated field evaluation

A small modification to what sam wisegamzee has shown could give that easily. Try like:

if(SingleValue <= 30,'30 and Below', 'More than 30') as INDICATOR

userid128223
Contributor

Re: Comma seperated field evaluation

yes, but it would be wrong.

I need individually by Product Value not by all SingleValue combined.