Modeling Excel data in a script

[Anonymous]

Dear All,

I have an Excel sheet with data like bellow:

Order,     Status A, Status B, Status C

1,              OK, NOK, NOK

2,               NOK,NOK,NOK

3,               OK,OK,OK

4,               NOK,OK,NOK

Can I generate a load script crating a table like bellow?

Status,     Order count

A-OK,          2

A-NOK,       2

B-OK,          2

B-NOK,       2

C-OK,          1

C-NOK,       3

I need it to generate a graphic indicating a volume of orders with NOK Status / Status type.

[Jason_Michaelides]

Sure - you need to use CrossTable()

Temp:

LOAD * From Excel...;

Data:

CrossTable(StatusGroup,Status)

LOAD * RESIDENT Temp;

DROP Table Temp;

This will give you a table with 3 fields - Order(1,2,3 etc), StatusGroup (A,B,C), and Status (NOK,OK).

In a straight table chart, use StatusGroup and Status as your dimensions, with Count(Order) as the expression.

Hope this helps,

Jason

[john_duffy · Accepted solution]

Hello.

Just to add to Jason's answer, you can concatenate the two dimensions in the load script in order to get the chart to display in the format 'A-OK', 'A-NOK', etc.

See the attached application.

John.

[Anonymous]

Jason,

Thanks the help but I don´t have one field Status, I have three Status types...

Order,     Status A, Status B, Status C

1,              OK, NOK, NOK

2,               NOK,NOK,NOK

3,               OK,OK,OK

4,               NOK,OK,NOK

[Anonymous]

Jason,

Perfect. Thanks !!