Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
reivax31
Partner
Partner

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

7 Replies
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;


Capture.PNG

Kush
MVP
MVP

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;

Kush
MVP
MVP

Just added

reivax31
Partner
Partner
Author

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

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;

View solution in original post

reivax31
Partner
Partner
Author

Thanks again for your help, It worked!