Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: Load only rows with min value

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;

20 Replies
ankit777
Valued Contributor

Re: Load only rows with min value

Hi

You can use firstsortedvalue function

Re: Load only rows with min value

Data:

LOAD ID,

         min(Amount) as Amount

FROM table

Group by ID

Re: Load only rows with min value

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

Re: Load only rows with min value

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?

Re: Load only rows with min value

LOAD ID,

          ID1,

          FirstSortedValue(Amount,Diff) as Amount

Group by ID,ID1;

Re: Load only rows with min value

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;

Re: Load only rows with min value

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

Re: Load only rows with min value

!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 !

Re: Load only rows with min value

How do you find the diff?

Community Browser