topic Re: assign variable to categorise values from data tables in QlikView

assign variable to categorise values from data tables

Mon, 30 Jun 2014 13:31:26 GMT

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

Re: assign variable to categorise values from data tables

Anonymous — Mon, 30 Jun 2014 13:42:57 GMT

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 ....;

Re: assign variable to categorise values from data tables

Tue, 01 Jul 2014 09:15:13 GMT

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?

Re: assign variable to categorise values from data tables

SunilChauhan — Tue, 01 Jul 2014 09:25:36 GMT

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

];

Re: assign variable to categorise values from data tables

jonathandienst — Tue, 01 Jul 2014 09:39:22 GMT

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

Re: assign variable to categorise values from data tables

Tue, 08 Jul 2014 08:20:20 GMT

Thanks Jonathan, simple and worked a treat