Running number

[datanibbler]

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

[jagan]

Hi,

Can you attach some sample data?

Regards,

Jagan.

[datanibbler]

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.

[MK_QSL]

Where is the attachment?

[datanibbler]

Oops ...

[datanibbler]

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

[jagan]

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.

[martinpohl]

hello datanibbler

see attached

regards