Max Date for each workflow id

[#1]

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

[Gysbert_Wassenaar]

maybe something like this:

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

[#3]

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.

[Gysbert_Wassenaar]

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.

[jagannalla]

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

[#6]

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

[jagannalla]

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.