topic Re: Count of seqential numbers? in QlikView

Count of seqential numbers?

Thu, 30 Aug 2012 13:33:37 GMT

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.

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 13:56:06 GMT

input:

LOAD TESTSYSTEMID,

DOSINGDAY

FROM

[day seq.xlsx]

(ooxml, embedded labels, table is Sheet1);

seqfalgs:

LOAD

TESTSYSTEMID,

DOSINGDAY,

if((DOSINGDAY=previous(DOSINGDAY)+1 and TESTSYSTEMID=previous(TESTSYSTEMID) and peek(SEQUENTAL)<>0) or

(TESTSYSTEMID<>previous(TESTSYSTEMID)), 1, 0) as SEQUENTAL

Resident input;

drop table;

seqcount:

LOAD

TESTSYSTEMID,

SUM(SEQUENTAL) as SEQCOUNT

Resident seqfalgs

group by TESTSYSTEMID;

Re: Count of seqential numbers?

Thu, 30 Aug 2012 14:05:48 GMT

Whiteline,

Thanks so much. But it doesnt seem to work correctly. There are only SEQENTAL counts of 0 and 1. I'll dive in to your script and have a think.

Cheers

Mark

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 14:08:29 GMT

Look carefuly.

The result is in SEQCOUNT.

Re: Count of seqential numbers?

Thu, 30 Aug 2012 14:20:47 GMT

It seems to be the ...

or (TESTSYSTEMID<>previous(TESTSYSTEMID))

statement that was screwing things around. I've removed this and things are looking pretty rosy.

I've just got to find the max of unique SEQENTALs and things are good.

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 14:41:00 GMT

I think you should replace it with

or (TESTSYSTEMID<>previous(TESTSYSTEMID) and DOSINGDAY=1)

or it will skip all first values.

Re: Count of seqential numbers?

Thu, 30 Aug 2012 15:07:22 GMT

This is looking good. But I have an issue when I tried it with a bigger data set.

If one of the patients has 3 days of sequential treatments then a break then 5 days of sequential treatments the function we have gives a value of 8. Whereas the value I am after is 5 in that case.

I will amend the data set in the original xls file so that patient1 has this issue.

Cheers

Mark

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 15:13:33 GMT

I've already post you the right expression:

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

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

Re: Count of seqential numbers?

Thu, 30 Aug 2012 15:28:37 GMT

Thanks Whiteline,

But when I run the expression as it stands I get in SEQCOUNT a count of DOSINGDAY (albeit as a count (SEQUENTAL)), not a count of the maximum sequential days that a patient recieved treatment.

Cheers!

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 15:32:30 GMT

What is wrong ?

Re: Count of seqential numbers?

Thu, 30 Aug 2012 15:38:26 GMT

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

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 15:50:18 GMT

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.

Re: Count of seqential numbers?

Thu, 30 Aug 2012 16:02:34 GMT

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!

Re: Count of seqential numbers?

whiteline — Thu, 30 Aug 2012 16:09:17 GMT

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;

Re: Count of seqential numbers?

Thu, 30 Aug 2012 16:14:45 GMT

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.