topic Re: Help Please in QlikView

Help Please

Wed, 11 Sep 2013 12:14:09 GMT

Hi All,

I Have an example of a Table like below:

Person	Parcel	Status
1	1	5
1	2	10
1	3	12
2	4	2
2	5	4
2	6	10
2	7	10
2	8	15
3	9	1
3	10	5
3	11	6
3	12	10
3	13	15

I Need to see if the last Parcel (Per Person) has a Status of '15'. Here I use Max(Parcel), but I need to get all the Parcels Prior to it that has a Status of '10'...

My Answer should look like this and should be in a Table (Straight or Pivot):

Person	Parcel	Status
2	6	10
2	7	10
3	12	10

Any Advice Please?

Re: Help Please

Wed, 11 Sep 2013 13:02:29 GMT

Anyone know how to do this?

Re: Help Please

jonathandienst — Wed, 11 Sep 2013 13:04:39 GMT

Use

=FirstSortedValue(Person, -Status)

=FirstSortedValue(Parcel, -Status)

Regards

Jonathan

Re: Help Please

tresB — Wed, 11 Sep 2013 13:05:01 GMT

Actually, the logic of calculation is not very clear to me. Why should not there be one following row?

Person	Parcel	Status
1	2	10

Re: Help Please

Wed, 11 Sep 2013 13:11:54 GMT

Tresesco,

Because I need all the parcels Prior to the Max(Parcels) with a Status 15 that has a Status of 10.

And Jonathan, This does not seem to work .

See attached example

Re: Help Please

tresB — Wed, 11 Sep 2013 13:18:15 GMT

I guess you have to use set analysis with p().

Re: Help Please

Wed, 11 Sep 2013 13:36:11 GMT

Hi , Please find the attached file. I added the FLAG in the script level as 1 for person who reached the Max(Status) else 0.