topic Re: Filter rows in QlikView

Filter rows

gidon500 — Mon, 18 Apr 2016 11:41:27 GMT

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

Re: Filter rows

swuehl — Mon, 18 Apr 2016 12:23:29 GMT

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;

Re: Filter rows

sunny_talwar — Mon, 18 Apr 2016 12:30:00 GMT

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;

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;

Re: Filter rows

swuehl — Mon, 18 Apr 2016 12:40:23 GMT

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" ?

Re: Filter rows

sunny_talwar — Mon, 18 Apr 2016 12:42:13 GMT

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

Re: Filter rows

swuehl — Mon, 18 Apr 2016 12:53:57 GMT

I am just kidding ...

Re: Filter rows

sunny_talwar — Mon, 18 Apr 2016 12:54:50 GMT

Re: Filter rows

gidon500 — Mon, 18 Apr 2016 13:59:38 GMT

thank you very much

gidon

Re: Filter rows

sunny_talwar — Mon, 18 Apr 2016 14:02:16 GMT

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