Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Remove unwanted rows in a table

Hi,

I have a straight table with content as shown below

     

CID          LIDDescStartDateEndDate
11Test30/06/201230/06/2013
12Test231/10/201331/10/2014
21Test30/11/200830/11/2013
22Test231/07/201431/07/2015
31Test29/02/200828/02/2013
32Test231/10/201331/10/2014
41Test31/08/201231/08/2013
42Test231/08/201431/08/2015

I would like to keep only one row for each CID, i.e the latest row of data.

Can someone please let me know how to do this in the front end and not in the script?

Thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: Remove unwanted rows in a table

1.png

5 Replies
krishna_2644
Valued Contributor III

Re: Remove unwanted rows in a table

You can try using FirstSortedValue()  function : FirstSortedValue(CID, StartDate).

let me know if that doesnt work

Not applicable

Re: Remove unwanted rows in a table

Razor , While leading the data, just load the data what you need.

Data:

LOAD

     CID,

     LID,

     DESC,

     StartDate,

     EndDate,

From Source;

Inner Join(DATA)

LOAD

     CID,

     Max(StartDate) AS StartDate

Resident DATA

Group By CID;

The above script keeps only latest record CID based on StartDate.

patroser
Contributor

Re: Remove unwanted rows in a table

You could work with max()

Taking CID, LID, Desc and StartDate as Dimensions and max(EndDate) as expression ends up in a straight table just holding the values of the latest EndDate.

MVP
MVP

Re: Remove unwanted rows in a table

1.png

Not applicable

Re: Remove unwanted rows in a table

That's working now. Thanks to everyone, especially Krishna and Massimo

Community Browser