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

1 Solution

Accepted Solutions
Kushal_Chawda

Data:

LOAD

ID

DateOne

SETDate

DateTwo,

floor(DateTwo)- floor(DateOne) as Diff,

Amount

FROM table;

New:

LOAD ID,

           DateOne,

           FirstSortedValue(DateTwo,Diff) as DateTwo,

          Firstsortedvalue(Amount,Diff) as Amount

Resident Data

group by ID,DateOne;

drop table Data;

View solution in original post

20 Replies
ankit777
Specialist
Specialist

Hi

You can use firstsortedvalue function

Kushal_Chawda

Data:

LOAD ID,

         min(Amount) as Amount

FROM table

Group by ID

tamilarasu
Champion
Champion

Hi Hans.


Try any one of the below methods,

Temp:

LOAD * INLINE [

    ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

];

NoConcatenate

LOAD ID,

FirstSortedValue(Amount, Amount)

Resident Temp Group by ID;

DROP Table Temp;


      OR


Temp:

LOAD * INLINE [

    ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

];

NoConcatenate

LOAD ID,

Min(Amount)

Resident Temp Group by ID;

DROP Table Temp;

Not applicable
Author

Okay and what about this...

ID, ID2,One, Two, Diff, Amount

    1, A, 10, 5, 5, 10

    1, A, 5, 2, 3, 25

    1, B, 6, 3, 3, 45

    1, B, 7, 3, 4, 45

    1, C, 8, 4, 4, 85

    2, A, 1, 0, 1, 50

I want to have following table:

ID, ID2, Amount

1, A, 25

1, B, 45

1, C, 85

2, A, 50

Based on min (Diff). How can I achive this?

Kushal_Chawda

LOAD ID,

          ID1,

          FirstSortedValue(Amount,Diff) as Amount

Group by ID,ID1;

tamilarasu
Champion
Champion

Fine.

Temp:

LOAD * INLINE [

   ID, ID2,One, Two, Diff, Amount

    1, A, 10, 5, 5, 10

    1, A, 5, 2, 3, 25

    1, B, 6, 3, 3, 45

    1, B, 7, 3, 4, 45

    1, C, 8, 4, 4, 85

    2, A, 1, 0, 1, 50

];

NoConcatenate

LOAD ID,

ID2,

Max(Amount)

Resident Temp Group by ID, ID2;

DROP Table Temp;

Kushal_Chawda

Hi Tamil,

what if the Data is? I think he is asking based on Min Diff

Temp:

LOAD * INLINE [

   ID, ID2,One, Two, Diff, Amount

    1, A, 10, 5, 5, 25

    1, A, 5, 2, 3, 10

    1, B, 6, 3, 3, 45

    1, B, 7, 3, 4, 45

    1, C, 8, 4, 4, 50

    2, A, 1, 0, 1, 80

];

Not applicable
Author

!Okay thank you for all the solutions but know you become my last example which im not able to finish. Look at teh excel sheet please. There you can see is a table on which every row is doubled because of the DateTwo join. Now I must choose the row where Diff is min. I have created a gap between the row we must compaire. I hope that anyone give me a solution about that problem.

Thank you !

Kushal_Chawda

How do you find the diff?