topic Re: Re: Re: Running number in QlikView

Running number

datanibbler — Fri, 14 Mar 2014 08:54:23 GMT

Hi guys,

I am searching for a function in QlikView to

=> return a running number - for the first "group of records" acc. to my conditions, there should be one, then for the next group there should be another and so on...

Can anyone help me there?

Thanks a lot!

Best regards,

DataNibbler

Re: Running number

jagan — Fri, 14 Mar 2014 08:59:09 GMT

Hi,

Can you attach some sample data?

Regards,

Jagan.

Re: Running number

datanibbler — Fri, 14 Mar 2014 09:09:11 GMT

Hi Jagan,

yes, I can. I have attached a small sample Excel_file which illustrates my purpose.

=> You see, one "sickness phase" is when I have one and the same PN and an uninterrupted series of days;

=> Every such phase should have a unique ID that I can consequently use for a GROUP BY clause when I want to

aggregate the whole table

=> There is a running_ID field there, but that is hard-coded which is of course not what I want to do actually.

Re: Running number

MK_QSL — Fri, 14 Mar 2014 09:10:51 GMT

Where is the attachment?

Re: Re: Running number

datanibbler — Fri, 14 Mar 2014 09:13:53 GMT

Oops ...

Re: Running number

datanibbler — Fri, 14 Mar 2014 09:31:38 GMT

Okay,

this is solved.

I made it using the AutoNumber() fct. and just applying it every time I encounter a new "group of records" and then always copying it from the record above until I encounter a new group yet again.

Best regards,

DataNibbler

Re: Running number

jagan — Fri, 14 Mar 2014 09:32:34 GMT

Hi,

Try this script

Temp:

LOAD PN,

Date,

[ID (should be like)]

FROM

HR_data_sickness_phases.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

If(RowNo() = 1, 1,If(Previous(PN) = PN AND Year(Date) = Year(Previous(Date)), Peek('RunningNumber'), Peek('RunningNumber') + 1)) AS RunningNumber

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.

Re: Re: Re: Running number

martinpohl — Fri, 14 Mar 2014 09:35:37 GMT

hello datanibbler

see attached

regards