Filter rows

[gidon500]

Hi guys

enclosed is a file where each part number has one or more lines

form this list I want to keep only line

with later DOCDATE date for each PARTNAME

FOR EXample

PARTNAME	fileNo	DOCDATE	Net Price
615732-B21	1	07/03/2016	134.54
615732-B21	1	15/04/2016	133.00
647594-B21	1	07/03/2016	276.00
647594-B21	1	15/04/2016	230.00

from this file I want to keep  the second and the forth lines as the date is later for each PARTNAME

615732-B21 , 1 , 15/04/2016 ,133.00

647594-B21 ,1,15/04/2015,230.00

enclosed the file

thanks

gidon

[swuehl]

There are multiple options, maybe like

SET DateFormat ='DD/MM/YYYY';

INPUT:
LOAD PARTNAME, 
     fileNo, 
     DOCDATE, 
     [Net Price]
FROM

(biff, embedded labels, table is Sheet1$);

LEFT JOIN (INPUT)
LOAD PARTNAME,
  Max(DOCDATE) as DOCDATE,
  1 as Flag
RESIDENT INPUT
GROUP BY PARTNAME;

RESULT:
NoConcatenate 
LOAD PARTNAME, 
     fileNo, 
     DOCDATE, 
     [Net Price]
Resident INPUT
WHERE Flag;

DROP TABLE INPUT;     

or like

SET DateFormat ='DD/MM/YYYY';

INPUT:
LOAD PARTNAME, 
     fileNo, 
     DOCDATE, 
     [Net Price]
FROM

(biff, embedded labels, table is Sheet1$);

LOAD PARTNAME,
  Max(DOCDATE) as DOCDATE,
  FirstSortedValue(fileNo,-DOCDATE) as FileNo,
  FirstSortedValue( [Net Price],-DOCDATE) as  [Net Price]
RESIDENT INPUT
GROUP BY PARTNAME;


DROP TABLE INPUT;     

[sunny_talwar]

Few other options:

Table:

LOAD PARTNAME,

     fileNo,

     DOCDATE,

     [Net Price]

FROM

[PART-SORT.xls]

(biff, embedded labels, table is Sheet1$);

Right Join (Table)

LOAD PARTNAME,

  fileNo,

  Date(Max(DOCDATE)) as DOCDATE

Resident Table

Group By PARTNAME, fileNo;

or

Table:

LOAD PARTNAME,

     fileNo,

     Date(Max(DOCDATE)) as DOCDATE,

     FirstSortedValue([Net Price], -DOCDATE) as [Net Price]

FROM

[PART-SORT.xls]

(biff, embedded labels, table is Sheet1$)

Group By PARTNAME, fileNo;

[swuehl]

Ok, it's good to avoid the resident loads.

But why group by PARTNAME and fileNo when he explictely asked for "keep only line with later DOCDATE date for each PARTNAME" ?

[sunny_talwar]

I wasn't sure if grouping by fileNo is needed or not, but since you did not have it, I thought I might as well add it just to add some variety

[swuehl]

I am just kidding ...

[sunny_talwar · Accepted solution]

[gidon500]

thank you very much

gidon

[sunny_talwar]

Gidon - Out of the two useful responses, why would you mark a smiley as a correct answer my friend? Please use this feature in a way so that this thread becomes useful for other people in the future.

Please go through this blog: Qlik Community Tip: Marking Replies as Correct or Helpful and see how important it is to mark correct and helpful answers for yourself and other users.

Thanks,

Sunny