Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable

Kindly reply

Specialist III
Specialist III

Try like:

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

hi pradip,

Its giving error invalid expression as group by is not used

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

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.