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
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 ?
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
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
;
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
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
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