Finding the Minimum date from a group of records.

[#1]

Hi All,

I'm new to QlikView and I have a task I need to complete, which I’m sure is
an easy one for you guys.  I need to pull out the minimum dates of a group
of records where Col2=S2.

Example:

Col1                      Col2       Col3

RO_0001             S1           4/15/2010

RO_0001             S2           3/19/2012

RO_0001             S2           1/10/2011

RO_0002             S2           3/25/2010

RO_0002             S2           8/8/2012

RO_0002             S1           10/10/2019

RO_0003             S1           12/12/2009

RO_0003             S2           10/01/2012

RO_0003             S2           06/05/2011

RO_0003             S2           05/05/2009

The result table should look like:

RO_0001             S2           1/10/2011

RO_0002             S2           3/25/2010

RO_0003             S2           05/05/2009

I would need the result table to be shown when the application is started on the dashboard by default not after a selection is made.

[MayilVahanan]

HI

Try like this

Test:

Load * Inline

[

Col1,Col2,Col3

RO_0001,S1,4/15/2010

RO_0001,S2,3/19/2012

RO_0001,S2,1/10/2011

RO_0002,S2,3/25/2010

RO_0002,S2,8/8/2012

RO_0002,S1,10/10/2019

RO_0003,S1,12/12/2009

RO_0003,S2,10/01/2012

RO_0003,S2,06/05/2011

RO_0003,S2,05/05/2009

]Where Col2='S2';

Load FirstSortedValue(Col3,Col3) as RequiredDate, Col1 Resident Test Group by Col1 Order by Col3;

Use in list box -> RequiredDate and in sort use load->reversed..

Hope that helps

[#3]

See attached example.

[#4]

Hello and thanks, but my example data would be a table that contains thousands of records.  I cannot use the inline because I don't know all of those values.

[#5]

Thanks for the reply, I can't use this method because my example data would contain thousands of records.

[#6]

Yours would not be an inline load.  You would just use the expression.  We just used the inline load in order to give you example data. In a straight table, put the following expession:

if(Col2='S2',Date(min({1}Col3),'MM/DD/YYYY'))

Except with your field names instead of 'Col2' and 'Col3' of course.

[#7]

Ok Great! Let me give it a try and I'll get back to.  Thanks again.

[#8]

I must be doing something wrong.  I'm still not getting the result i'm looking for.  In the above example, the records need to be grouped together by Col1, Filtered by Col2 and the minimum date pulled out of each group.  When I add the expression.. if(DVACTIVITYTYPE=1,Date(min({1}DVACTDATE),'MM/DD/YYYY'))  to the straight table, I run out of memory.  Col1 or INVENTDIMID needs to be grouped.  Thanks for your help.

[MayilVahanan]

Hi

Did you try the firstsortedValue function in script?

[#10]

No, but I will try that and let you know.  Thanks again, I really appreciate the help from you guys.