topic Agrr on Peek in QlikView

Agrr on Peek

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I have a following table

A B

1 a

1 b

1 c

2 a

2 b

3 a

and I want a following output

A B Flag

1 a -

1 b -

1 c 1

2 a -

2 b 1

3 a 1

Please can someone help me in getting the above output.

Thanking everyone in anticipation

Regards

Karunpreet

Re: Agrr on Peek

MarcoWedel — Sun, 20 Mar 2016 17:47:58 GMT

Hi,

maybe something like this?

table1:
LOAD RecNo() as ID, * INLINE [
    A, B
    1, a
    1, b
    1, c
    2, a
    2, b
    3, a
];

Left Join (table1)
LOAD *, 1 as Flag
Resident table1
Where A<>Previous(A)
Order By ID desc;

DROP Field ID;

hope this helps

regards

Marco

Re: Agrr on Peek

maxgro — Sun, 20 Mar 2016 18:08:49 GMT

another solution could be

table1:

LOAD * INLINE [

A, B

1, a

1, b

1, c

2, a

2, b

3, a

];

Left Join (table1)

LOAD A, MaxString(B) as B, 1 as Flag

Resident table1

Group By A;

Re: Agrr on Peek

MarcoWedel — Sun, 20 Mar 2016 19:50:00 GMT

can you elaborate on the condition that defines your flag?

regards

Marco

Re: Agrr on Peek

swuehl — Sun, 20 Mar 2016 20:12:48 GMT

Yes, a short explanation how the flag is defined would be more than helpful.

Assuming that it's flagging the last record per dimension A in load order:

table1:
LOAD * INLINE [
    A, B
    1, a
    1, b
    1, c
    2, a
    2, b
    3, a
];

Left Join (table1)
LOAD A, LastValue(B) as B, 1 as Flag
Resident table1
Group By A;

Re: Agrr on Peek

jagan — Mon, 21 Mar 2016 03:38:19 GMT

Hi,

Try this script

Data:

LOAD *,

RowNo() AS Sno

INLINE [

A, B

1, a

1, b

1, c

2, a

2, b

3, a

];

LEFT JOIN(Data)

LOAD

Max(Sno) AS Sno,

1 AS Flag

RESIDENT Data

Group By A;

Regards,

Jagan.