topic Re: Load only rows with min value in QlikView

Load only rows with min value

Fri, 20 May 2016 09:18:18 GMT

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

Re: Load only rows with min value

ankit777 — Fri, 20 May 2016 09:21:38 GMT

You can use firstsortedvalue function

Re: Load only rows with min value

Kushal_Chawda — Fri, 20 May 2016 09:22:01 GMT

Data:

LOAD ID,

min(Amount) as Amount

FROM table

Group by ID

Re: Load only rows with min value

tamilarasu — Fri, 20 May 2016 09:26:46 GMT

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;

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;

Re: Load only rows with min value

Fri, 20 May 2016 10:25:26 GMT

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

Kushal_Chawda — Fri, 20 May 2016 10:29:08 GMT

LOAD ID,

ID1,

FirstSortedValue(Amount,Diff) as Amount

Group by ID,ID1;

Re: Load only rows with min value

tamilarasu — Fri, 20 May 2016 10:33:03 GMT

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

Kushal_Chawda — Fri, 20 May 2016 10:36:24 GMT

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

];

Re: Load only rows with min value

Fri, 20 May 2016 11:15:21 GMT

!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

Kushal_Chawda — Fri, 20 May 2016 11:20:48 GMT

How do you find the diff?

Re: Load only rows with min value

Kushal_Chawda — Fri, 20 May 2016 11:25:30 GMT

Data:

LOAD

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

Re: Load only rows with min value

Fri, 20 May 2016 11:47:18 GMT

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

Re: Load only rows with min value

Kushal_Chawda — Fri, 20 May 2016 12:00:52 GMT

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

Re: Load only rows with min value

Fri, 20 May 2016 12:37:45 GMT

Oh sorry. I mean DateTwo

Re: Load only rows with min value

Kushal_Chawda — Fri, 20 May 2016 12:40:21 GMT

date2 is already there.. see firstsortedvalue

Re: Load only rows with min value

tamilarasu — Fri, 20 May 2016 12:45:21 GMT

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.

Re: Load only rows with min value

tamilarasu — Fri, 20 May 2016 12:46:28 GMT

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;

Re: Load only rows with min value

Fri, 20 May 2016 12:47:18 GMT

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

Re: Load only rows with min value

tamilarasu — Fri, 20 May 2016 12:54:22 GMT

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;

Re: Load only rows with min value

Kushal_Chawda — Fri, 20 May 2016 12:54:25 GMT

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