Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
See attached example.
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.
Thanks for the reply, I can't use this method because my example data would contain thousands of records.
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.
Ok Great! Let me give it a try and I'll get back to. Thanks again.
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.
Hi
Did you try the firstsortedValue function in script?
No, but I will try that and let you know. Thanks again, I really appreciate the help from you guys.