topic Re: Modeling Excel data in a script in QlikView

Modeling Excel data in a script

Anonymous — Thu, 21 Jun 2012 16:00:27 GMT

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.

Re: Modeling Excel data in a script

Jason_Michaelides — Thu, 21 Jun 2012 17:42:00 GMT

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

Re: Modeling Excel data in a script

john_duffy — Thu, 21 Jun 2012 17:55:07 GMT

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.

Re: Modeling Excel data in a script

Anonymous — Thu, 21 Jun 2012 18:00:39 GMT

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

Re: Modeling Excel data in a script

Anonymous — Thu, 21 Jun 2012 18:18:54 GMT

Jason,

Perfect. Thanks !!