Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Getting the max value of continuous data

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

1.png

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;

View solution in original post

8 Replies
marcus_sommer

Are the data normal in a column or within a single field-value?

- Marcus

Not applicable
Author

Hi Marcus,

in a column.

Thank you

nagarjuna_kotha
Partner - Specialist II
Partner - Specialist II

Hi Thorsten,

Can you share sample file ,thn only we can solve it easily.

-Nagarjun

marcus_sommer

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

maxgro
MVP
MVP

1.png

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;

MarcoWedel

Hi,

maybe also helpful:

QlikCommunity_Thread_196398_Pic1.JPG

QlikCommunity_Thread_196398_Pic2.JPG

QlikCommunity_Thread_196398_Pic3.JPG

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

Not applicable
Author

Thank you very much!

Kind regards

Thorsten

MarcoWedel

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:

QlikCommunity_Thread_196398_Pic4.JPG

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