topic Re: Get second minimum datetime in QlikView

Get second minimum datetime

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Get second minimum datetime

chaper — Tue, 01 Sep 2015 02:49:19 GMT

Can you post a sample file

Re: Get second minimum datetime

chaper — Tue, 01 Sep 2015 02:57:56 GMT

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

Re: Get second minimum datetime

qlikviewwizard — Tue, 01 Sep 2015 04:00:12 GMT

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

Re: Get second minimum datetime

sunny_talwar — Tue, 01 Sep 2015 04:00:21 GMT

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

];

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

Re: Get second minimum datetime

Anonymous — Tue, 01 Sep 2015 12:33:47 GMT

Hi all,

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

Re: Get second minimum datetime

Anonymous — Tue, 01 Sep 2015 16:10:08 GMT

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.

Re: Get second minimum datetime

Anonymous — Tue, 01 Sep 2015 23:36:07 GMT

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.