topic Re: Getting the max value of continuous data in QlikView

Getting the max value of continuous data

Sat, 12 Dec 2015 18:33:55 GMT

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

Re: Getting the max value of continuous data

marcus_sommer — Sun, 13 Dec 2015 15:56:30 GMT

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

- Marcus

Re: Getting the max value of continuous data

Sun, 13 Dec 2015 15:57:27 GMT

Hi Marcus,

in a column.

Thank you

Re: Getting the max value of continuous data

nagarjuna_kotha — Sun, 13 Dec 2015 15:59:47 GMT

Hi Thorsten,

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

-Nagarjun

Re: Getting the max value of continuous data

marcus_sommer — Sun, 13 Dec 2015 17:10:36 GMT

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

Re: Getting the max value of continuous data

maxgro — Sun, 13 Dec 2015 17:30:34 GMT

source2:

load rowno() as id, s2 inline [

];

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

MarcoWedel — Mon, 14 Dec 2015 00:13:39 GMT

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

Re: Getting the max value of continuous data

Tue, 15 Dec 2015 16:09:34 GMT

Thank you very much!

Kind regards

Thorsten

Re: Getting the max value of continuous data

MarcoWedel — Tue, 15 Dec 2015 22:15:22 GMT

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