topic Re: Get Max Status by Date and Fill Values in QlikView

Get Max Status by Date and Fill Values

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Get Max Status by Date and Fill Values

sunny_talwar — Wed, 14 Dec 2016 14:11:18 GMT

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;

Re: Get Max Status by Date and Fill Values

microwin88x — Wed, 14 Dec 2016 14:15:24 GMT

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!

Re: Get Max Status by Date and Fill Values

sunny_talwar — Wed, 14 Dec 2016 14:16:51 GMT

Just change this:

Left Join (Table)

LOAD D_TCKT,

Min(MonthYear) as Min,

MonthName(Today()) as Max

Resident Table

Group By D_TCKT;

Re: Get Max Status by Date and Fill Values

microwin88x — Wed, 14 Dec 2016 14:18:03 GMT

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

Re: Get Max Status by Date and Fill Values

sunny_talwar — Wed, 14 Dec 2016 14:18:54 GMT

Sounds like a plan