topic Re: How to find latest status for Cases in QlikView

How to find latest status for Cases

manojkulkarni — Fri, 08 Sep 2017 07:03:37 GMT

Hi Friends,

I have set of case IDs along with status per day in which i need to identify the distinct cases id with referred status only. The cases referred and closed on the same day should not be counted. Below is the sample data along with expected output.

Appreciate any suggestion

ID	Status	Date
K1	Referred	02-11-2015
K1	Closed	02-11-2015
K2	Referred	02-11-2015
K3	Referred	02-11-2015
K3	Referred	02-11-2015
K4	Closed	02-11-2015
K5	Open	02-11-2015

Expected Output

ID	Status
K2	Referred
K3	Referred

Re: How to find latest status for Cases

its_anandrjs — Fri, 08 Sep 2017 07:15:21 GMT

Try this

MainTable:

Load * Inline

[

ID, Status, Date

K1, Referred, 02-11-2015

K1, Closed, 02-11-2015

K2, Referred, 02-11-2015

K3, Referred, 02-11-2015

K4, Closed, 02-11-2015

K5, Open, 02-11-2015

];

Final:

Load

ID,Date,

If(ID = Previous(ID) and Status = Previous(Status), Status) as Status

Resident Where Status = 'Referred';

//This Drop table is optional

Drop Table MainTable;

Re: How to find latest status for Cases

Kushal_Chawda — Fri, 08 Sep 2017 07:18:40 GMT

Data:

LOAD ID,

Status,

Date

FROM table;

left join(Data)

LOAD ID,

Cont(ID) as CntID

resident Data

group by ID;

Now you can use below expression in chart with ID as dimension

=only({<CntID={1},status={'Referred'}>} Status)

Re: How to find latest status for Cases

Kushal_Chawda — Fri, 08 Sep 2017 07:21:12 GMT

or on front end

Dimension:

Expression:

= only ({<ID={"=count(ID)=1"},Status={'Referred'}>} Status)

Re: How to find latest status for Cases

antoniotiman — Fri, 08 Sep 2017 07:21:55 GMT

Hi Manoj,

try this

Only({<ID=P({<Status={'Referred'}>})>} Status)

Regards,

Antonio

Re: How to find latest status for Cases

rohitraut — Fri, 08 Sep 2017 07:34:13 GMT

Hello Manoj,

Try this,

In Calculated Dimension,

=Aggr(if(Status <> 'Closed'and Status <>'Open' and Status='Referred',ID),ID)

Re: How to find latest status for Cases

manojkulkarni — Fri, 08 Sep 2017 07:39:21 GMT

Thanks Antonio, This worked. How can we get the count of referred cases ?

Re: How to find latest status for Cases

antoniotiman — Fri, 08 Sep 2017 07:44:09 GMT

In Text Box

=Count({<ID=P({<Status={'Referred'}>})>} DISTINCT Status)

Re: How to find latest status for Cases

effinty2112 — Fri, 08 Sep 2017 07:45:52 GMT

Hi Manoj,

Another suggestion:

ID	if(aggr(Concat(DISTINCT Status,','),ID,Date)= 'Referred',Status)
K2	Referred
K3	Referred

Cheers

Andrew

Re: How to find latest status for Cases

Kushal_Chawda — Fri, 08 Sep 2017 07:46:10 GMT

or try this

only ({<ID={"=count({<Status={'Referred'}>}ID)>=1"}>} Status)

Re: How to find latest status for Cases

manojkulkarni — Fri, 08 Sep 2017 10:29:54 GMT

Thanks Antonio