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

[shekhar_analyti]

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 .

[shekhar_analyti]

Hello Sunny Bhai 
@sunny_talwar 

[skamath1]

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 ;

[Kushal_Chawda · Accepted solution]

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 ...............;

[sunny_talwar]

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);