Finding the Max EndDate of the Request

[mdmukramali]

Dear All,

I have 3 Tables in my sample application.

1--->  REQUEST TABLE

2---> SECTION TABLE

3---> TASK TABLE

REQUEST table contain the REQUEST_NO ,REQUEST_DATE AND REQUEST_STATUS.

IF THE STATUS IS EQUAL TO 3 THEN THAT REQUEST IS FINISHED BUT THE END DATE OF THAT REQUEST IS AVAILABLE IN TASK TABLE. (TASK END DATE)

SO THE CONDITION IS

IF THE REQUEST_STATUS =3 THEN GET THE MAXIMUM END DATE FOR THAT REQUEST_NO FROM TASK TABLE.(TASK END DATE)

i am trying to bring the MAX(TASK_END_DATE) Into REQUEST TABLE WHERE REQUEST_STATUS =3

find the attached sample file.

Thanks,

Mukram.

[rubenmarin · Accepted solution]

Hi Mukram,

Perhaps you can use some of this code:

// Creating MAP_EndDates

REQUEST:

LOAD Distinct REQUEST_NO

FROM

[.\SERVICE REQUEST.xlsx]

(ooxml, embedded labels, table is REQUEST)

Where REQUEST_STATUS=3;

Left Join (REQUEST)

LOAD REQUEST_NO, Date(Max(TASK_END_DATE)) as TASK_END_DATE

FROM

[.\SERVICE REQUEST.xlsx]

(ooxml, embedded labels, table is TASK)

Group By REQUEST_NO;

MAP_EndDates:

Mapping LOAD * Resident REQUEST;

DROP Table REQUEST;

// Loading data

REQUEST:

LOAD REQUEST_NO,

     REQUEST_TYPE_CODE,

     REQUEST_STATUS,

     REQUEST_DATE,

     ApplyMap('MAP_EndDates', REQUEST_NO, Null()) as EndDate

FROM

[.\SERVICE REQUEST.xlsx]

(ooxml, embedded labels, table is REQUEST);

[mdmukramali]

Dear Ruben,

Thanks it is working perfectly.

Thanks,

Mukram.