Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load only rows with min value

Hi guys,

I'm trying to load "min values" from a table. I have redundant rows but I want only the row's which have the min Amount.

Example:

  ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

In this Example I want only back...

1,2

2,3

3,9

But I don't know to handle this.

Thank you

20 Replies
Kushal_Chawda



Data:

LOAD

ID

DateOne

SETDate

DateTwo,

floor(DateTwo)- floor(DateOne) as Diff

FROM table;

New:

LOAD ID,

           DateOne,

           FirstSortedValue(DateTwo,Diff) as DateTwo

Resident Data

group by ID,DateOne;


drop table Data;



Make sure that your Date fields are in proper format

          

Not applicable
Author

Thank you for that. There is only one thing missing. I need the SETDate in the New/Final Table...

Kushal_Chawda

Add the setdate in load and add it in group by also

Not applicable
Author

Oh sorry. I mean DateTwo

Kushal_Chawda

date2 is already there.. see firstsortedvalue

tamilarasu
Champion
Champion

Hi Kushal,

Sorry for the late reply. I started to home so couldn't reply.  I have misunderstood the question. Thanks for pointing my mistake.

tamilarasu
Champion
Champion

Hi Hans,

Try this,

Data:

LOAD ID,

     DateOne,

     SETDate,

     DateTwo,

     DateOne-DateTwo as Diff

FROM

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

Temp:

Inner Keep

Load ID,

DateOne,

Max(Diff) as Diff

Resident Data

Group by ID, DateOne;


DROP Table Temp;

Not applicable
Author

Now I see it's my fault. In the table there is a column missing -> Amount. I need Amount in my final table

tamilarasu
Champion
Champion

Try this,

Data:

LOAD ID,

     DateOne,

     SETDate,

     DateTwo,

     DateOne-DateTwo as Diff,

     Amount

FROM

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

Temp:

Inner Keep

Load ID,

DateOne,

Max(Diff) as Diff

Resident Data

Group by ID, DateOne;

DROP Table Temp;

Kushal_Chawda

Do below change in my above post

Add Amount field in Data table

Then add one more field amount in New table as below

firstsortedvalue(Amount,Diff) as Amount