Taking Max Date

[#1]

Hello All,

     I need to find For particular Contract number maximum of Date where transaction_Code is T668 or TJE6.

As shown in image for 00040501 for T668 i should get date as 07/06/2014,How can i write condition in backend for this??

I used  below condition but its giving error. Kindly help.

if(mixmatch(BATCH_TRANSACTION_CODE,'T668','TJE6'),max(Date))

AS I_Date

[#2]

Kindly reply

[senpradip007]

Try like:

Max(if(mixmatch(BATCH_TRANSACTION_CODE,'T668','TJE6'),Date))

AS I_Date

[#4]

hi pradip,

Its giving error invalid expression as group by is not used

[aveeeeeee7en]

Hi

Use something like this:

TABLE1:
Load * Inline [
Contract_Number, Transaction_Code, NewDate
00040501, T668, 05/29/14
00040501, T668, 06/05/14
00040501, T668, 06/07/14
00040501, T669, 06/09/14
00040502, T668, 07/10/14
00040502, T668, 07/15/14
00040502, T668, 07/27/14
00040502, T669, 07/29/14
];

TABLE2:
NoConcatenate
Load
Contract_Number AS New_Contract_Number,
Transaction_Code AS New_Transaction_Code,
Date(Max(NewDate),'DD/MM/YYYY') AS I_Date
Resident TABLE1 WHERE Match(Transaction_Code,'T668','TJE6')
Group By Contract_Number, Transaction_Code;
Drop Table TABLE1;

Following is the Snapshot of the Output:

Also, see the Attachment.

Regards

Av7eN

[jagan]

Hi,

Try like this

Data:

Load

Contract_Number, Transaction_Code, Date

FROM DataSource;

LEFT JOIN (Data)

LOAD

Transaction_Code,

Date(Max(Date)) AS NewMaxDate

RESIDENT Data

where mixmatch(Transaction_Code,'T668','TJE6')

GROUP BY Transaction_Code;

Regards,

Jagan.