Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
varunreddy
Creator III
Creator III

How to display a value based on the maximum date for same Issue

Hi Guys,

I am trying to pick the maximum status for an issue based on the maximum Date available. Please find the attachment below.

I have 3 fields Date, Status and ID and below is my dataset.

Date          Status                 ID

Mar-17      In Progress        123

Apr-17      In Progress        123

Mar-17     Opened              245

Apr-17      In Progress        245

I want to see this as my output:

Date          Status                 ID

Apr-17      Resolved            123

Apr-17      In Progress        245

For an ID, I need the status for max Date of an ID.

I have attached data source and QVW I created. I am getting status for min date and I am not sure, Where I am doing it wrong.

Can anyone please take a look at this?

Thanks in advance!

Regards,

Varun

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi varun,

Maybe

Date(Max(Date),'MMM-YY') FirstSortedValue(Status,-Date) ID
Apr-17Resolved123
Apr-17In Progress245

View solution in original post

18 Replies
sunny_talwar

Try this

Table:

LOAD Date,

    Status,

    ID

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (Table)

LOAD ID,

Max(Date) as Date

Resident Table

Group By ID;

Anil_Babu_Samineni

May be this?

Dimension is MonthName(Date) and second dimension

Expression - Only({<Date = {'$(=Max(Date))'}>} ID)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
sunny_talwar

Other way and probably a slightly more efficient one

Table:

LOAD Date,

    Status,

    ID

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where ID <> Previous (ID)

Order By ID, Date desc;

DROP Table Table;

antoniotiman
Master III
Master III

Hi Varun,

Maybe this

Regards,

Antonio

Kushal_Chawda

Data:

LOAD date(Date, 'MMM-YYYY') as Date,

     Status,

     ID

FROM

[Book1 (2).xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD date(max(Date),'MMM YYYY') as Date,

     ID,

     1 as Flag

Resident Data

Group by ID



Create straight table


Dimension:

ID


Expression

1) Date

=Only({<Flag={1}>}Date)


2) Status

=Only({<Flag={1}>}Status)

sunny_talwar

Did I miss something, all of you are giving front end solutions... did the OP specifically asked for front end solution? I am sorry if I missed it

sunny_talwar

Just of curiosity

Kushal_Chawda

Mine is mixed, front and back end

varunreddy
Creator III
Creator III
Author

Hi Anil,

I am having different filters in the expression I am using. Can we accommodate in your expression?

count({<[Pull Type]= {'M'},[Open Age] = {'> 0 & <= 30 Days'},[DQ Item Type] = {'DQ Issues'},Severity = {'Critical','High'},
[Resolution Due Date Month Year] -= {'UNASSIGNED'},
Status -= {'Draft','Cancelled'}>}distinct Id)