topic Re: need help in load script in QlikView

need help in load script

Sat, 15 Aug 2015 17:41:07 GMT

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

Re: need help in load script

maximiliano_vel — Sat, 15 Aug 2015 17:53:13 GMT

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;

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;

Re: need help in load script

Sat, 15 Aug 2015 17:54:22 GMT

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

Re: need help in load script

maximiliano_vel — Sat, 15 Aug 2015 18:00:14 GMT

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;