topic How to pick first row for each key values and store separately ? in QlikView

How to pick first row for each key values and store separately ?

shekhar_analyti — Fri, 10 Jul 2020 12:56:46 GMT

Hi All ,

What should be the script to pick any first row for each key values and store separately in QVD .

Sample data with expected output below :

LOAD * INLINE [

Key, M1, M2, LOC, RATIO, DIM
K1, 8, 3, E, 4:3, A
K1, 2, 4, W, 4:4, B
K1, 7, 10, N, 4:5, A
K1, 11, 9, S, 4:6, C
K2, 3, 1, S, 4:7, D
K3, 5, 4, E, 4:8, D
K3, 6, 5, E, 4:9, M
K3, 1, 1, S, 4:10, O
];

Thank You .

Re: How to pick first row for each key values and store separately ?

shekhar_analyti — Fri, 10 Jul 2020 13:02:39 GMT

Hello Sunny Bhai
@sunny_talwar

Re: How to pick first row for each key values and store separately ?

skamath1 — Fri, 10 Jul 2020 13:11:44 GMT

Data:
Load *, If(Previous(Key)=Key, 1,0) as KC
;

LOAD * INLINE [

Key, M1, M2, LOC, RATIO, DIM
K1, 8, 3, E, 4:3, A
K1, 2, 4, W, 4:4, B
K1, 7, 10, N, 4:5, A
K1, 11, 9, S, 4:6, C
K2, 3, 1, S, 4:7, D
K3, 5, 4, E, 4:8, D
K3, 6, 5, E, 4:9, M
K3, 1, 1, S, 4:10, O
];

FirstRowData:
NoConcatenate
LOAD Key, M1, M2, LOC, RATIO, DIM
Resident Data where KC =0 ;

Re: How to pick first row for each key values and store separately ?

Kushal_Chawda — Fri, 10 Jul 2020 13:22:32 GMT

you many need to use order by to order your data first so that peek and previous function can work as expected

Data: LOAD * INLINE [ Key, M1, M2, LOC, RATIO, DIM K1, 8, 3, E, 4:3, A K1, 2, 4, W, 4:4, B K1, 7, 10, N, 4:5, A K1, 11, 9, S, 4:6, C K2, 3, 1, S, 4:7, D K3, 5, 4, E, 4:8, D K3, 6, 5, E, 4:9, M K3, 1, 1, S, 4:10, O ]; Final: load * where Flag=1; load *, if(peek(Key)<>Key,1,0) as Flag resident Data order by Key; drop table Data; Store Final Into ...............;

Re: How to pick first row for each key values and store separately ?

sunny_talwar — Fri, 10 Jul 2020 15:06:22 GMT

One more option

Table: LOAD * INLINE [ Key, M1, M2, LOC, RATIO, DIM K1, 8, 3, E, 4:3, A K1, 2, 4, W, 4:4, B K1, 7, 10, N, 4:5, A K1, 11, 9, S, 4:6, C K2, 3, 1, S, 4:7, D K3, 5, 4, E, 4:8, D K3, 6, 5, E, 4:9, M K3, 1, 1, S, 4:10, O ] Where not Exists(Key);