Count of seqential numbers?

[#1]

Hi all,

I am trying to figure out how to make a count of and then find the max of only groups of sequential numbers. Ideally for the load script but in a function is fine too.

The data looks like this...

PatientID , TreatmentDay

1     , 1

1      , 2

1     ,3

1     ,4

1     ,6

1     ,7

1     ,8

2     ,1

2     ,2

2     ,3

2     ,4

2     ,5

The correct value for patient1 would be 4 and for  patient 2 5

I attach a xls to this post with a basic data set in,

Any help appreciated.

[#2]

Well TESTSYSTEM 1 has 5 DOSINGDAY s that are sequential Day3-7. So the count for that individual should be 5.

TESTSYSTEM 2 has 6 sequential days Day1-6

TESTSYSTEM 3 has 6 sequential days Day1-6

[whiteline]

Ok, I thought you want only those that starts from 1

if((DOSINGDAY=previous(DOSINGDAY)+1 and TESTSYSTEMID=previous(TESTSYSTEMID) and

peek(SEQUENTAL)<>0) or (TESTSYSTEMID<>previous(TESTSYSTEMID) and DOSINGDAY=1), 1, 0)  as SEQUENTAL

But if there will be two sets of sequental days it will sum it.

[#4]

Yes cheers, I had amended your code to to do the same. Ideally I would like to just find the max of each "group"  of sequential values rather than the sum. I'll think about it over the weekend. Thanks agina!

[whiteline · Accepted solution]

Ok, I hope thats final.

input:

LOAD TESTSYSTEMID,

     DOSINGDAY

FROM

[day seq.xlsx]

(ooxml, embedded labels, table is Sheet1);

seqfalgs:

LOAD

TESTSYSTEMID,

DOSINGDAY,

if(TESTSYSTEMID=previous(TESTSYSTEMID), if(DOSINGDAY-previous(DOSINGDAY)=1, 1, 0), 0) as SEQUENTAL

Resident input

order by TESTSYSTEMID, DOSINGDAY;

drop table;

seqstring:

LOAD

TESTSYSTEMID,

Concat(SEQUENTAL, '', DOSINGDAY) as SEQSTRING

Resident seqfalgs

group by TESTSYSTEMID;

sunseqs:

LOAD

TESTSYSTEMID,

len(SubField(SEQSTRING, '0'))+1 as SEQLEN

Resident seqstring;

LOAD

TESTSYSTEMID,

Max(SEQLEN) as MAXSEQLEN

Resident sunseqs

group by TESTSYSTEMID;

[#6]

Dude you are awesome. Thanks for the code, plus, by looking at your code, I've learnt alot about loading data, so even more thanks.