Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
actually it was quite difficult to describe my question in the header
I have a big dataset (which I already calculated) and I'd like to identify the max of continuous data:
Example data table:
0,1,1,0,1,1,1,0,0,1,1,0,1,1,1,1,0
So the result shoud be 4 - Is that possible to calculate it within a table?
Kind regards and thank you very much
Thorsten
source2:
load rowno() as id, s2 inline [
s2
0,
1,
1,
0,
1,
1,
1,
0,
0,
1,
1,
0,
1,
1,
1,
1,
0,
];
Left Join (source2) load
id, if(s2=0, '-', alt(Peek('cum'),0) + s2) as cum
Resident source2
order by id;
Are the data normal in a column or within a single field-value?
- Marcus
Hi Marcus,
in a column.
Thank you
Hi Thorsten,
Can you share sample file ,thn only we can solve it easily.
-Nagarjun
With peek/previous you could access the previous record and check if this value = value from previous record (other fields like an ID might be included, too) - see: Peek() or Previous() ?
- Marcus
source2:
load rowno() as id, s2 inline [
s2
0,
1,
1,
0,
1,
1,
1,
0,
0,
1,
1,
0,
1,
1,
1,
1,
0,
];
Left Join (source2) load
id, if(s2=0, '-', alt(Peek('cum'),0) + s2) as cum
Resident source2
order by id;
Hi,
maybe also helpful:
tabData:
LOAD RecNo() as ID,
Round(Rand()) as data
AutoGenerate 1000;
tabGroups:
LOAD ID,
GroupNum,
AutoNumber(RecNo(),GroupNum) as GroupSeqNum
Where data;
LOAD ID,
data,
If(data=1 and Previous(data)<>1,RangeSum(Peek(GroupNum),1),Peek(GroupNum)) as GroupNum
Resident tabData
Order By ID;
Left Join (tabGroups)
LOAD GroupNum,
Max(GroupSeqNum) as GroupSize
Resident tabGroups
Group By GroupNum;
regards
Marco
Thank you very much!
Kind regards
Thorsten
Hi,
another version without using the Autonumber() function due to performance issues for large groupnumbers (see swuehl's comment here: Re: Reloading qvw much slower when using Autonumber) could be:
tabData:
LOAD RecNo() as ID,
Round(Rand()) as data
AutoGenerate 100000;
tabGroups:
LOAD ID,
GroupNum,
RecNo()-GroupStartRec+1 as GroupSeqNum,
GroupStartRec
Where data;
LOAD ID,
data,
RangeSum(Peek(GroupNum),-GroupStart) as GroupNum,
If(GroupStart,RecNo(),Peek(GroupStartRec)) as GroupStartRec;
LOAD ID,
data,
data=1 and Previous(data)<>1 as GroupStart
Resident tabData
Order By ID;
Left Join (tabGroups)
LOAD GroupNum,
Max(GroupSeqNum) as GroupSize
Resident tabGroups
Group By GroupNum;
hope this helps
regards
Marco