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

[varunreddy]

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

[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)

[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]

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]

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)