topic Re: Taking Max Date in QlikView

Taking Max Date

Tue, 07 Oct 2014 01:35:47 GMT

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

Re: Taking Max Date

Tue, 07 Oct 2014 04:32:52 GMT

Kindly reply

Re: Taking Max Date

senpradip007 — Tue, 07 Oct 2014 04:35:14 GMT

Try like:

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

AS I_Date

Re: Taking Max Date

Tue, 07 Oct 2014 04:57:04 GMT

hi pradip,

Its giving error invalid expression as group by is not used

Re: Taking Max Date

aveeeeeee7en — Tue, 07 Oct 2014 05:00:22 GMT

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

Re: Taking Max Date

jagan — Tue, 07 Oct 2014 05:19:45 GMT

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.