Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
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
hi,
PFa.
$@M.
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.
My question might be misleading.
I just need 2 indicators.
1) Product Value > 30
2) Product Value < 30
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
yes, but it would be wrong.
I need individually by Product Value not by all SingleValue combined.