Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Taking Max Date

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

comm5.PNG.png

5 Replies
Highlighted
Not applicable

Kindly reply

Highlighted
Specialist III
Specialist III

Try like:

Max(if(mixmatch(BATCH_TRANSACTION_CODE,'T668','TJE6'),Date))AS I_Date
Highlighted
Not applicable

hi pradip,

Its giving error invalid expression as group by is not used

Highlighted
Specialist III
Specialist III

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:

Max date Data.png

Also, see the Attachment.

Regards

Av7eN

Highlighted
MVP & Luminary
MVP & Luminary

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.