Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Getting the max value of continuous data

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;

8 Replies

Re: Getting the max value of continuous data

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

- Marcus

Not applicable

Re: Getting the max value of continuous data

Hi Marcus,

in a column.

Thank you

nagarjuna_kotha
Valued Contributor II

Re: Getting the max value of continuous data

Hi Thorsten,

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

-Nagarjun

Re: Getting the max value of continuous data

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

MVP
MVP

Re: Getting the max value of continuous data

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;

Re: Getting the max value of continuous data

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

Re: Getting the max value of continuous data

Thank you very much!

Kind regards

Thorsten

Re: Getting the max value of continuous data

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

Community Browser