topic Re: Comma seperated field evaluation in QlikView

Comma seperated field evaluation

userid128223 — Thu, 21 Jul 2016 21:53:47 GMT

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

Re: Comma seperated field evaluation

Thu, 21 Jul 2016 22:25:44 GMT

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

Re: Comma seperated field evaluation

userid128223 — Thu, 21 Jul 2016 22:49:58 GMT

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.

Re: Comma seperated field evaluation

rwunderlich — Fri, 22 Jul 2016 00:28:46 GMT

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

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Comma seperated field evaluation

userid128223 — Fri, 22 Jul 2016 04:17:35 GMT

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

Re: Comma seperated field evaluation

mightyqlikers — Fri, 22 Jul 2016 04:40:43 GMT

hi,

PFa.

$@M.

Re: Comma seperated field evaluation

userid128223 — Fri, 22 Jul 2016 04:51:51 GMT

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.

Re: Comma seperated field evaluation

userid128223 — Fri, 22 Jul 2016 05:05:44 GMT

My question might be misleading.

I just need 2 indicators.

1) Product Value > 30

2) Product Value < 30

Re: Comma seperated field evaluation

tresB — Fri, 22 Jul 2016 05:11:35 GMT

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

Re: Comma seperated field evaluation

userid128223 — Fri, 22 Jul 2016 05:27:22 GMT

yes, but it would be wrong.

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

Re: Comma seperated field evaluation

tresB — Fri, 22 Jul 2016 05:32:01 GMT

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 ?

Re: Comma seperated field evaluation

userid128223 — Fri, 22 Jul 2016 12:31:14 GMT

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

Re: Comma seperated field evaluation

Ralf-Narfeldt — Fri, 22 Jul 2016 12:46:43 GMT

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

;

Re: Comma seperated field evaluation

PrashantSangle — Fri, 22 Jul 2016 13:06:54 GMT

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

Re: Comma seperated field evaluation

Anonymous — Fri, 22 Jul 2016 13:30:15 GMT

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

Re: Comma seperated field evaluation

rwunderlich — Fri, 22 Jul 2016 16:17:08 GMT

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