Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gidon500
Contributor II

Filter rows

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

   

PARTNAMEfileNoDOCDATENet Price
615732-B21107/03/2016134.54
615732-B21115/04/2016133.00
647594-B21107/03/2016276.00
647594-B21115/04/2016230.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

1 Solution

Accepted Solutions

Re: Filter rows

8 Replies
MVP
MVP

Re: Filter rows

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

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;


Capture.PNG

MVP
MVP

Re: Filter rows

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

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

MVP
MVP

Re: Filter rows

I am just kidding ...

Re: Filter rows

gidon500
Contributor II

Re: Filter rows

thank you very much

gidon

Re: Filter rows

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

Community Browser