assign variable to categorise values from data tables

[#1]

I'm looking at ways to "categorise" values from my data, possibly using a variable! To give an example of what I want to achieve:

I have a field called "Work Order Status" from a spreadsheet I've loaded.

Records can have a Status value of: "In progress", "Assigned", "Awaiting Finance Approval", "On Hold", "Closed", Complete", "Cancelled" etc.

What I want is for the values from the "Work Order Status" field to be categorised to be either "Open" or Closed" to make things
easier when I'm creating charts etc based on the status of the work order jobs.

Can I set something up like a Variable such as vStatusOpen for following Status values "Assigned", "Awaiting Finance Approval",
"On Hold". And vStatusClosed for "Closed", Complete", "Cancelled".

And if so what’s best way to create it in the script etc?

Thanks

Dan

[Anonymous]

why not something like:

yourtable:

load

[Work Order Status],

if(Match([Work Order Status], 'Assigned', 'Awaitin Finance Approval', 'On Hold'), 1, 0) as [Status Open],

if(Match([Work Order Status], 'Closed', 'Complete','Cancelled'), 1, 0) as [Status Closed],

....

from ....;

[#3]

Would this mean the value of "Status Open" or "Status Closed" is added in another column in the table, as I do not want to overwrite the existing values in the {Work Order Status] column as these are used elsewhere?

[SunilChauhan]

Tab1:

Load Status,

if(Status='In progress' or Status='Assigned' or  Status='Awaiting Finance Approval' or Status='On Hold','Open') as statusopen,

if(Status='Closed' or Status='Complete' or  Status='Cancelled' ,'closed') as statusClosed;

LOAD * INLINE [

Status

In progress

Assigned

Awaiting Finance Approval,

On Hold,

  Closed

Complete

Cancelled

];

[jonathandienst · Accepted solution]

Hi

I would create a field for the category, and include it in your model by joining the list to the main data table, like this:

Join(Data)

LOAD *

Inline

[

  Work Order Status, Category

  Assigned, Open

  Awaiting Finance Approve, Open

  On Hold, Open

  Closed, Closed

  Complete, Closed

  Cancelled, Closed

];

This assumes that the main data table has already been loaded. Change the table name from 'Data' to the correct name. Change the field name Category if that is not a suitable name. No need for variables. Add a list box with this new field to your model.

HTH

Jonathan

[#6]

Thanks Jonathan, simple and worked a treat