MIN and Group By

[reivax31]

Hi Qlikers,

I would like to know how I can fin the Responsible for the MIN(creation date) and Type=A

Table is (result in yellow)

AncestorID	Type	creation date	responsible	Min date	Min Responsible
1	A	11/04/2016	Ben	11/04/2016	Ben
1	A	12/04/2016	Tom	11/04/2016	Ben
1	B	13/04/2016	Jim
1	A	14/04/2016	Ben	11/04/2016	Ben
1	C	15/04/2016	Jim
1	C	16/04/2016	Tom

load
AncestorID,

MIN(IF([Type]='A',[creation date])) AS [Min date],

..... AS  [Min Responsible]

Resident test  Group By AncestorID ;

Thanks for your help

[sunny_talwar]

Try this:

Table:

LOAD AncestorID,

     Type,

     [creation date],

     responsible

FROM

[https://community.qlik.com/thread/212749]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD Type,

  Date(Min([creation date])) as [Min Date],

  FirstSortedValue(responsible, [creation date]) as [Min Responsible]

Resident Table

Where Type = 'A'

Group By Type;

[Kushal_Chawda]

slight modification. Add AncestorID as well in group by

Table:

LOAD AncestorID,

    Type,

    [creation date],

    responsible

FROM

[https://community.qlik.com/thread/212749]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD AncestorID,

          Type,

  Date(Min([creation date])) as [Min Date],

  FirstSortedValue(responsible, [creation date]) as [Min Responsible]

Resident Table

Where Type = 'A'

Group By AncestorID,Type;

[sunny_talwar]

And in the load?

Left Join (Table)

LOAD AncestorID,

  Type,

  Date(Min([creation date])) as [Min Date],

  FirstSortedValue(responsible, [creation date]) as [Min Responsible]

Resident Table

Where Type = 'A'

Group By AncestorID,Type;

[Kushal_Chawda]

Just added

[reivax31]

Hi,

Sorry for my late reply. But I was trying to make this solution work with my data. So the solution you gave me works perfectly fine as long as there are no double (Min([creation date]) . In my case, most of the time, I will have several Min([creation date]) for a same AncestorID.

So what Qlik does, is to leave [Min Responsible] blank because several solutions are possible. So I was trying to use Concat to have my [Min Responsible] with all the possible values.

Or, other option, is to force Qlik to pick the first value possible. So, if I have several responsible at a same (Min([creation date]), I want to pick at least one value (doesn't matter wich one).

But for now it doesn't work yet...

[sunny_talwar · Accepted solution]

Try adding a DISTINCT to the FirstSortedValue() function

Table:

LOAD AncestorID,

    Type,

    [creation date],

    responsible

FROM

[https://community.qlik.com/thread/212749]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD Type,

  Date(Min([creation date])) as [Min Date],

  FirstSortedValue(DISTINCT responsible, [creation date]) as [Min Responsible]

Resident Table

Where Type = 'A'

Group By Type;

or

Table:

LOAD AncestorID,

    Type,

    [creation date],

    responsible

FROM

[https://community.qlik.com/thread/212749]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD AncestorID,

  Type,

  Date(Min([creation date])) as [Min Date],

  FirstSortedValue(DISTINCT responsible, [creation date]) as [Min Responsible]

Resident Table

Where Type = 'A'

Group By AncestorID, Type;

[reivax31]

Thanks again for your help, It worked!