topic Re: Detect same value in sequence - pivot in QlikView

Detect same value in sequence - pivot

Mon, 22 Aug 2011 10:53:18 GMT

Hello QVer,
I need another help because I tried a lot with interrecord functions but without success.

There is a pivot table with three dimensions (ID, KODEDATE, KODE) sorted chronologically by KODEDATE.
The rule is that no KODE should be followed by itself over OPSDATE as you can see for ID1.
ID2 shows a sequence of always the same KODE (=99821) at different KODEDATE, this is wrong and I will find it out an mark it.

Can You help please. Thanks.
dj_skbs

Detect same value in sequence - pivot

Mon, 22 Aug 2011 11:10:39 GMT

Hi,

You could try the following expression:

if(Kode=above(Kode),'F','')

This only works if the Kode field is sorted to show the same Kode's in adjectant rows.

Otherwise some work in the script is needed.

Regards,

Bert

Detect same value in sequence - pivot

Mon, 22 Aug 2011 11:50:07 GMT

Thanks. But this won't work.

There's condition that KODE should be sorted dependent on KODEDATE.

Any further idea?

dj_skbs

Detect same value in sequence - pivot

swuehl — Mon, 22 Aug 2011 12:01:31 GMT

Have you tried Bert's suggestion?

I think it will work if table is like shown above. And his comment on sort order is in accordance with your sort, isn't it?

Re: Detect same value in sequence - pivot

Mon, 22 Aug 2011 13:21:50 GMT

Thanks. Yes I tried Bert's expression in my original QVW and had my own unsuccessful experiences that way before.
I will send a little example QVW.
Condition:
No KODE (sort by KODEDATE) should be followed by the same value.
So KODE (ID2) = 99821 (11.01.2011 / 01.+ 08.02.2011) should be marked as false.

Let me say the requirements in other words:
For each ID KODEs will be documented beginning with the first day of ID presence.
When necessary another KODEs will be documented day(s) later but never the same KODE like the direct precursor.

Hope it's understandable.
dj_skbs

Detect same value in sequence - pivot

Mon, 22 Aug 2011 13:23:17 GMT

Try using three columns

CODICE PREVCODICE FLAG

SUM(KODE) ABOVE(SUM(KODE)) IF(CODICE=PREVCODICE,'F','')

Re: Detect same value in sequence - pivot

lironbaram — Mon, 22 Aug 2011 13:31:36 GMT

hei

check this one

i find it that in cases like yours is better to do the calculation

in the load script

hope its helps you

Re: Detect same value in sequence - pivot

swuehl — Mon, 22 Aug 2011 13:34:42 GMT

If you move the KODE column from dimension to expression, it is working (that's what I had assumed). Or do you need to group by KODE?

Please see attached.

Re: Detect same value in sequence - pivot

swuehl — Mon, 22 Aug 2011 13:49:37 GMT

And even if you need KODE as dimension, this expression should work, then

=if(KODE=above(total KODE),'F','')

The total is needed here.

Regards,

Stefan

Re: Detect same value in sequence - pivot

Mon, 22 Aug 2011 14:48:13 GMT

Thank You all. Stefan's proposal is optimal for me; I didn't recognise that variant.

Here are the differences between swuehl and liron baram in

dj_skbs