Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you post a sample file
You can try using min(FieldValue('OrderDateTime','2')) in expression.
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);
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?
Hi all,
Sorry I got little occupied. I will post a sample file as soon as possible. Thank you.
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.
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.