Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator 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
chaper
Creator III
Creator III

Can you post a sample file

chaper
Creator III
Creator III

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

qlikviewwizard
Master II
Master II

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

sunny_talwar

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
Creator II
Creator II
Author

Hi all,

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

star_limit
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.