topic Re: Select rows with min in QlikView

Select rows with min

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need help with aggregating my table:

From this table I need only the yellow lines with a minimum in "Diff_Date" in each product-Group. And if there are two min, like product A1, I need both lines.

How can i manage this in my script?

Re: Select rows with min

sunny_talwar — Tue, 19 Sep 2017 14:26:07 GMT

May be this

Table:

LOAD Diff_Date,

[Date-1],

[Date-2],

Key_Product

FROM ....;

Right Join (Table)

LOAD Key_Product,

Min(Diff_Date) as Diff_Date

Resident Table

Group By Key_Product;

Updated my response based on fvelascog72 comment who pointed out that i missed Group By statement

Re: Select rows with min

antoniotiman — Tue, 19 Sep 2017 14:30:29 GMT

LOAD *

From Table

Where Diff_Date <= 1;

Re: Select rows with min

Anonymous — Wed, 20 Sep 2017 06:07:27 GMT

Thanks, but the smalles number could be any number. Only in the example it is 0 and 1.

Re: Select rows with min

fvelascog72 — Wed, 20 Sep 2017 06:23:43 GMT

Hi,

The same solution plus GROUP BY.

Table1:

LOAD * Inline [

Date_1, Date_2, Diff_Date, Key_Product

01/01/2017, 01/01/2017, 1, Product B

02/01/2017, 02/01/2017, 7, Product B

03/01/2017, 03/01/2017, 0, Product A1

04/01/2017, 04/01/2017, 16, Product A1

05/01/2017, 05/01/2017, 0, Product A2

01/02/2017, 01/02/2017, 36, Product A2

02/02/2017, 02/02/2017, 2, Product C

03/02/2017, 03/02/2017, 0, Product A1

];

Right Join

LOAD

Min(Diff_Date) as Diff_Date,

Key_Product

Resident Table1

Group by Key_Product;

Saludos

Re: Select rows with min

Anonymous — Wed, 20 Sep 2017 09:21:50 GMT

Thanks! This works and I learnd something new!