Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shekhar_analyti
Specialist
Specialist

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

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 :

FIRST ROW.PNG

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 .

1 Solution

Accepted Solutions
Kushal_Chawda

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

 

View solution in original post

4 Replies
shekhar_analyti
Specialist
Specialist
Author

Hello Sunny Bhai 
@sunny_talwar 

skamath1
Creator III
Creator III

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

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