Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

reivax31
Contributor III

MIN and Group By

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)

     

AncestorIDTypecreation date responsibleMin dateMin Responsible
1A11/04/2016Ben11/04/2016Ben
1A12/04/2016Tom11/04/2016Ben
1B13/04/2016Jim
1A14/04/2016Ben11/04/2016Ben
1C15/04/2016Jim
1C16/04/2016Tom

load
AncestorID,

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

..... AS  [Min Responsible]

Resident test  Group By AncestorID ;

Thanks for your help

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: MIN and Group By

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;

7 Replies
MVP
MVP

Re: MIN and Group By

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;


Capture.PNG

Re: MIN and Group By

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;


MVP
MVP

Re: MIN and Group By

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;

Re: MIN and Group By

Just added

reivax31
Contributor III

Re: MIN and Group By

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

MVP
MVP

Re: MIN and Group By

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
Contributor III

Re: MIN and Group By

Thanks again for your help, It worked!