Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
star_limit
Contributor II

Get second minimum datetime

Hi All,

I have the below code and I get the first correct minimum OrderDateTime. I am trying to get the second OrderDateTime. Could someone please assist? Thanks.

LOAD

  CustomerID,

  ProductID,

  MIN(OrderDateTime) AS OrderDateTime,

MIN(OrderDateTime,2) AS OrderDateTime2

RESIDENT Orders

GROUP BY CustomerID,ProductID;

Expression1:

ONLY({<NumberID={'400'}>} OrderDateTime)  //this gives me the correct 1st minimum OrderDateTime

Expression2:

ONLY({<NumberID={'400'}>} OrderDateTime2) //Gives me null even though there is a second OrderDateTime. NumberID comes from another table inner joined.

7 Replies
chaiperi
Contributor III

Re: Get second minimum datetime

Can you post a sample file

chaiperi
Contributor III

Re: Get second minimum datetime

You can try using min(FieldValue('OrderDateTime','2')) in expression.

qlikviewwizard
Honored Contributor II

Re: Get second minimum datetime

Hi Please check the below code:

Table:

LOAD EMPNO,

    ENAME,

  JOB,

    MGR,

    date(HIREDATE) as HIREDATE,

    Timestamp(HIREDATE) as MinDate,

    SAL,

    COMM,

  DEPTNO

FROM

[EMP.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Capture.JPG

Re: Get second minimum datetime

This small sample I created seems to work:

Table:

LOAD CustomerID,

  ProductID,

  Min(OrderDateTime) AS OrderDateTime,

  Min(OrderDateTime,2) AS OrderDateTime2

Group By CustomerID,ProductID;

LOAD * Inline [

CustomerID, ProductID, OrderDateTime

A, ABC, 10

A, ABC, 30

A, ABC, 20

B, DEF, 5

B, DEF, 43

B, DEF, 23

];


Capture.PNG

May be the issue is with the join. Can you share some sample data to test out what is going wrong?

star_limit
Contributor II

Re: Get second minimum datetime

Hi all,

Sorry I got little occupied. I will post a sample file as soon as possible. Thank you.

star_limit
Contributor II

Re: Get second minimum datetime

Seems like I don't have the upload rights yet. I created a similar file and all works in the sample but it still doesn't work with my work code. Let me paste below the similar working code and the one I am trying to use at work that is not working.

Work Code NOT giving me the second minimum date from Tab 2

//Tab1

Table1:
LOAD
CustomerID,
NumberID,
ProductID,
BatchResult,
BatchDate;
SQL SELECT CustomerID,NumberID,ProductID, BatchResult,BatchDate
FROM Table1
WHERE NumberID IN ('1','2','3');

INNER JOIN(Table1)
LOAD
CustomerID,
MIN(BatchDate) AS BatchDate,
NumberID
RESIDENT Table1
GROUP BY CustomerID, NumberID;

//Tab2

INNER JOIN (Table1)
LOAD
CustomerID,
ProductID,
OrderDateTime;
SQL SELECT CustomerID,ProductID,OrderDateTime
FROM LabSpecimens;

INNER JOIN(Table1)
LOAD
CustomerID,
ProductID,
MIN(OrderDateTime) AS OrderDateTime,
MIN(OrderDateTime,2) AS OrderDateTime2
RESIDENT Table1
GROUP BY CustomerID,ProductID;

3 Expressions
ONLY({<NumberID={'1'}>} BatchResult)
ONLY({<NumberID={'2'}>} OrderDateTime)
ONLY({<NumberID={'3'}>} OrderDateTime2)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sample code that worked when I create a similar prototype

Orders:

LOAD CustomerID,

     NumberID,

     ProductID,

     MIN(BatchDate) as BatchDate,

     BatchResult

GROUP BY CustomerID,NumberID,ProductID,BatchResult;

LOAD * Inline [

CustomerID,  NumberID,ProductID,BatchDate,BatchResult

A,1, 10,08/01/2015,Good

A,2, 20,08/02/2015,Fair

A,3, 30,08/03/2015,Bad

B,1, 10,08/04/2015,Good

B,2, 20,08/05/2015,Fair

B,3, 30,08/06/2015,Bad

];

Inner join(Orders)

LOAD CustomerID,

     ProductID,

     Min(OrderDateTime) AS OrderDateTime,

     Min(OrderDateTime,2) AS OrderDateTime2

Group By CustomerID,ProductID;

LOAD * Inline [

CustomerID, ProductID, OrderDateTime

A, 10, 09/01/2015

A, 10, 09/02/2015

A, 20, 09/03/2015

A, 20, 09/04/2015

A, 30, 09/05/2015

A, 30, 09/06/2015

B, 20, 09/07/2015

B, 20, 09/08/2015

B, 30, 09/09/2015

];

3 Expressions:

ONLY({<NumberID={'1'}>} BatchResult)

ONLY({<NumberID={'1'}>} OrderDateTime)

ONLY({<NumberID={'1'}>} OrderDateTime2)

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Please assist. Thanks.

star_limit
Contributor II

Re: Get second minimum datetime

I found that CustomerID has same NumberID but different ProductIDs in my work database. If the a CustomerID and NumberID has same ProductIDs then the second min value can be extracted but in my work database the ProductIDs for the second OrderDateTime will be different so I am still scratching my head on how I will fetch the second minimum OrderDateTime that has a different ProductID with those 2 ProductIDs having same CustomerID and NumberID.