Complex requirement on Object level report

[ashok1203]

Hi All,

I have a requirement which are consists of ID, Status, Create and Update Dates.

From the source am getting more no.of records on same ID's. In that records i need to filter the data based on Max (Update Date) followed with Priority Status.

Priority Status:

Act

Rejected XYZ

Pending XYZ

Rejected ABC

Pending ABC

Dft

Example:    

ID	Status	Create_Date	Update_Date
1	Act	9/7/2016 4:40:56 AM	10/6/2016 4:04:03 AM
1	pending xyz	9/7/2016 4:40:56 AM	10/6/2016 4:04:03 AM
1	Dft	9/7/2016 4:40:56 AM	10/6/2016 4:03:05 AM

In the above data, for ID "1" i need to show the record below.

Required Output:    

ID	Status	Create_Date	Update_Date
1	Act	9/7/2016 4:40:56 AM	10/6/2016 4:04:03 AM

The conditions to filter the data is

- Consider the maximum Update Date Value

- Still we are getting more no. of records for the same ID then based on the Priority Status we need to show the appropriate status value for the ID then we will get only one record for the ID with latest update date and latest Status.

Attached a sample data and follows by the desired output. Please let me know for more clarification on this.

Thanks

[settu_periasamy]

Try this

MAP_Order_Priority:
Mapping LOAD Upper([Order priority]) as [Order priority],RecNo() as Rec;
LOAD * INLINE [
Order priority
Act
Rejected XYZ
Pending XYZ
Rejected ABC
Pending ABC
Dft
];

MAP_Order:
Mapping LOAD RecNo() as Rec,Upper([Order priority]) as [Order priority];
LOAD * INLINE [
Order priority
Act
Rejected XYZ
Pending XYZ
Rejected ABC
Pending ABC
Dft
];
Temp:
LOAD ID,
Status,
ApplyMap('MAP_Order_Priority',Upper(Status),0) as Order,
Create_Date,
Update_Date
FROM
Report.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

T1:

LOAD ID,
Capitalize(ApplyMap('MAP_Order',Min(Order),'Unknown')) as Status,
Max(Create_Date) as Create_Date,
Max(Update_Date) as Update_Date,
Min(Order) as Order
Resident Temp Group by ID Order by ID;

DROP Table Temp;

DROP Field Order;

[ashok1203]

Thanks Periyasamy. It's working fine for me as per requirement.

[ashok1203]

Hi Periyasamy, Their is some addition to the above requirement. I need to show the latest value if the latest Update date is single value. But in the above scenario, we are finding the status irrespective of single latest date or not. now the requirement look like initially we find all the latest records according to the latest update date, In that if we get multiple id values then we will identify the status according to the given order. Attached the sample requirement. Can you please help me on this.

[rahulpawarb]

Hello Ashokkumar,

Please refer the below script which will generate desired result using JOIN:

Report:

LOAD ID,

    Status,

    Match(UPPER(Status),'ACT', 'REJECTED XYZ', 'PENDING XYZ', 'REJECTED ABC', 'PENDING ABC', 'DFT') AS Position,

    Create_Date,

    Update_Date

FROM

Report.xlsx

(ooxml, embedded labels, table is Sheet1);

//Find the Max. Update Date for indivial IDs

INNER JOIN

LOAD ID AS ID,

    MAX(TIMESTAMP(Update_Date)) AS Update_Date

RESIDENT Report

GROUP BY ID;

//If we get multiple rows for the same Update Date then we need to take the Priority of the Status.

INNER JOIN

LOAD ID,

    MIN(Position) AS Position

RESIDENT Report

GROUP BY ID;

Hope this will help.

Thank you!

Rahul