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: 
Not applicable

need help in load script

is there a way to load only row with max value in table having dupicate records for same values for a particular ID column. i want this to be done in load script

ID, Col1,Col2,

1,aa,17

1,bb,34

1,aa,12

2,hh,14

2,hh,23

i want the output as

ID,Col1,Col2

1,bb,34

2,hh,23

1 Solution

Accepted Solutions
maximiliano_vel
Partner - Creator III
Partner - Creator III

Data:

LOAD * INLINE [

ID, Col1,Col2,

1,aa,17

1,bb,34

1,aa,12

2,hh,14

2,hh,23

];

Result:

LOAD  ID,

      FirstSortedValue(Col2, -Col2) as MaxCol2,

      FirstSortedValue(Col1, -Col2) as MaxCol1

RESIDENT Data

GROUP BY ID;

DROP Table Data;

View solution in original post

3 Replies
maximiliano_vel
Partner - Creator III
Partner - Creator III

Try

Data:

LOAD * INLINE [

ID, Col1,Col2,

1,aa,17

1,aa,34

1,aa,12

2,hh,14

2,hh,23

];

LOAD  ID,

      Max(Col2) as MaxCol,

      Col1

RESIDENT Data

GROUP BY ID, Col1;

DROP Table Data;

OR

Data:

LOAD * INLINE [

ID, Col1,Col2,

1,aa,17

1,aa,34

1,aa,12

2,hh,14

2,hh,23

];

LOAD  ID,

      FirstSortedValue(Col2, -Col2) as MaxCol,

      Col1

RESIDENT Data

GROUP BY ID, Col1;

DROP Table Data;

Not applicable
Author

Thanks i just realised that the values of col1 may vary hence updated the data in discussion and this is sample i am having few more columns in my actual data

maximiliano_vel
Partner - Creator III
Partner - Creator III

Data:

LOAD * INLINE [

ID, Col1,Col2,

1,aa,17

1,bb,34

1,aa,12

2,hh,14

2,hh,23

];

Result:

LOAD  ID,

      FirstSortedValue(Col2, -Col2) as MaxCol2,

      FirstSortedValue(Col1, -Col2) as MaxCol1

RESIDENT Data

GROUP BY ID;

DROP Table Data;