Get Max Status by Date and Fill Values

[microwin88x]

Hello,

I have the following table with:

- ID_TCKT: ID from Ticket

- Status: Status from Ticket

- DATE_STATUS: DD/MM/YYYY

- TIME_STATUS: hh:mm:ss

ID_TCKT	STATUS	DATE_STATUS	TIME_STATUS
TCKT01	In Progress	15/07/2016	14:29:33
TCKT01	Completed	07/10/2016	14:00:19
TCKT01	Closed	11/10/2016	02:45:53
TCKT02	In Progress	02/05/2016	19:51:00
TCKT02	Completed	02/07/2016	21:53:48
TCKT02	Closed	06/09/2016	02:53:15
TCKT03	Opened	10/05/2016	14:29:33
TCKT03	Scheduled	15/05/2016	14:00:19
TCKT03	Planned	20/05/2016	02:45:53
TCKT03	Completed	05/07/2016	19:51:00
TCKT03	Closed	07/07/2016	19:53:00

What I need is to get the STATUS for each Ticket for every MAX MONTH (DATE_STATUS)

And then fill that STATUS the next MONTHS until there's a new STATUS (have a look at TCKT01).

I should get a table like the following (I'd need to have fields: ID_TCKT, STATUS, YEAR and MONTH)

ID_TCKT	STATUS	YEAR	MONTH	DATE_STATUS	TIME_STATUS
TCKT01	In Progress	2016	Jul	15/07/2016	14:29:33
TCKT01	In Progress	2016	Aug
TCKT01	In Progress	2016	Sep
TCKT01	Closed	2016	Oct	11/10/2016	02:45:53
TCKT02	In Progress	2016	May	02/05/2016	19:51:00
TCKT02	In Progress	2016	Jun
TCKT02	Completed	2016	Jul	02/07/2016	21:53:48
TCKT02	Completed	2016	Aug
TCKT02	Closed	2016	Sep	06/09/2016	02:53:15
TCKT03	Planned	2016	May	20/05/2016	02:45:53
TCKT03	Planned	2016	Jun
TCKT03	Closed	2016	Jul	07/07/2016	19:53:00

Do you know how to do that on Script?

Thank you!

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD *,

  Month(DATE_STATUS) as Month,

  MonthName(DATE_STATUS) as MonthYear;

LOAD * INLINE [

    D_TCKT, STATUS, DATE_STATUS, TIME_STATUS

    TCKT01, In Progress, 15/07/2016, 14:29:33

    TCKT01, Completed, 07/10/2016, 14:00:19

    TCKT01, Closed, 11/10/2016, 02:45:53

    TCKT02, In Progress, 02/05/2016, 19:51:00

    TCKT02, Completed, 02/07/2016, 21:53:48

    TCKT02, Closed, 06/09/2016, 02:53:15

    TCKT03, Opened, 10/05/2016, 14:29:33

    TCKT03, Scheduled, 15/05/2016, 14:00:19

    TCKT03, Planned, 20/05/2016, 02:45:53

    TCKT03, Completed, 05/07/2016, 19:51:00

    TCKT03, Closed, 07/07/2016, 19:53:00

];

Right Join(Table)

LOAD D_TCKT,

  MonthYear,

  FirstSortedValue(STATUS, -DATE_STATUS) as STATUS

Resident Table

Group By D_TCKT, MonthYear;

Left Join (Table)

LOAD D_TCKT,

  Min(MonthYear) as Min,

  Max(MonthYear) as Max

Resident Table

Group By D_TCKT;

TempTable:

LOAD D_TCKT,

  Month(AddMonths(Min, IterNo()-1)) as Month

Resident Table

While AddMonths(Min, IterNo()-1) <= Max;

Left Join (TempTable)

LOAD *

Resident Table;

FinalTable:

LOAD D_TCKT,

  If(Len(Trim(STATUS)) = 0,

  If(D_TCKT = Previous(D_TCKT), Peek('STATUS'), STATUS), STATUS) as STATUS,

  DATE_STATUS,

  TIME_STATUS,

  Month

Resident TempTable

Order By D_TCKT, Month;

DROP Table Table, TempTable;

[microwin88x]

Hello Sunny, thank you!

And if I need to fill the Status until the current Month, how would you do that?

For example, for TCKT01, having "Closed" for Oct, Nov and Dec.

Thanks!

[sunny_talwar]

Just change this:

Left Join (Table)

LOAD D_TCKT,

  Min(MonthYear) as Min,

  MonthName(Today()) as Max

Resident Table

Group By D_TCKT;

[microwin88x]

Excellent! Thanks a lot. I'm going to have a look at it!

[sunny_talwar]

Sounds like a plan