topic Re: Max Date for each workflow id in QlikView

Max Date for each workflow id

Thu, 08 Nov 2012 10:59:36 GMT

Hi All,

Below is example data from my Dashboard. i want to write an expression which defines whether a case is open or closed.

What i need to do is get the the most recent start date for the each workflow id and then see what the description is and decide whether it is open or closed.

For example using the data below. i would want it to return.

Workflow ID 1 - Closed

Workflow ID 2 - Open

Workflow ID 3 - Open

Thanks

Laura

Workflow ID	Start Date	End Date	Description
1	10/01/2012 16:00:05	10/01/2012 16:10:02	new case
1	10/01/2012 16:10:02	10/01/2012 16:10:05	query
1	10/01/2012 16:10:05	15/01/2012 11:03:13	closed
2	12/01/2012 12:40:05	12/01/2012 16:00:05	new case
2	12/01/2012 16:00:05		customer order
3	10/01/2012 16:00:05		new case

Re: Max Date for each workflow id

Gysbert_Wassenaar — Thu, 08 Nov 2012 11:09:39 GMT

maybe something like this:

if(aggr(FirstSortedValue([Description],-[Start Date]),[Workflow ID])='Closed','Closed','Open')

Re: Max Date for each workflow id

Thu, 08 Nov 2012 11:17:29 GMT

Hi,

Thats great however what if there is more than 1 description which can be closed.

i.e the order is closed if the last decription is one of the following:

order complete

workflow complete

dealer paid

customer order confirmation

rejected

cancelled

etc.

Re: Max Date for each workflow id

Gysbert_Wassenaar — Thu, 08 Nov 2012 11:37:18 GMT

In that case you should consider adding a field in the load script and fill it using a mapping table

ClosedMap:

mapping load * [

order complete, 1

workflow complete, 1

dealer paid, 1

customer order confirmation, 1

rejected, 1

cancelled, 1

...etc.

];

Workflows:

load

StartDate,

EndDate,

Description

applymap('ClosedMap',Description,0) as ClosedYesNo

...etc.

from ....

Then the field ClosedYesNo can be used instead of Description to see if a workflow is closed. It can be done with a nested if statement, but this is a better solution imo.

Re: Max Date for each workflow id

jagannalla — Thu, 08 Nov 2012 11:48:53 GMT

Hello,

Hope this helps you.

WorkFlow:
LOAD [Workflow ID],
     [Start Date],
     [End Date],
     Description
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet4);
Map1:
Mapping
LOAD [Start Date],
If(Description='closed','Closed','Open') as Description
Resident WorkFlow;
ResWorkFlow:
LOAD [Workflow ID],ApplyMap('Map1',Date(Max([Start Date]),'MM-DD-YYYY hh:mm:ss')) as NewColumn
Resident WorkFlow
Group By [Workflow ID]

Cheers!!

Jagan Nalla

Re: Max Date for each workflow id

Thu, 08 Nov 2012 13:24:26 GMT

Hi the mapping does not work. how would a nested if statement look?

Re: Max Date for each workflow id

jagannalla — Thu, 08 Nov 2012 13:28:59 GMT

If possible can you attach sample file with data file. It hepls to work b'coz we don't know in which format you have dates.