topic Is it possible to create a cross table from a SQL server table in QlikView

Is it possible to create a cross table from a SQL server table

Tue, 15 Mar 2011 13:23:10 GMT

I'd like to be able to display something like this

Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Name1	20	9	10	0	0	0	0	0	0	0	0	0
Name2	25	10	20	0	0	0	0	0	0	0	0	0
Name 2	30	11	30	0	0	0	0	0	0	0	0	0

Monthly Totals	75	30	60	0	0	0	0	0	0	0	0	0
Yearly Total	165

I've seen how to create a cross table using the wizard in the Edit Script dialog but this looks like it needs the data already formatted.

Can I create either a chart or table view like this if the data not already formatted and exists is a SQL server table.

I am using the personal edition.

Any advice appreciated

Is it possible to create a cross table from a SQL server table

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 13:33:21 GMT

Hello Mark,

The short answer is yes. Although you don't specify a LOAD part in your script, is what actually QlikView will implicitly do when you do any SQL SELECT statement. So I'd create anyway the LOAD part formatting when necessary so QlikView gets all the information ready to load.

By the way, what formatting do you want to do? Something like

CROSSTABLE (ShortMonthName, MonthlyTotal) LOAD *;SQL SELECT Name, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, DecFROM Database.table;

should work, although you will need to do some cleanup removing the totals.

Hope that helps.

Is it possible to create a cross table from a SQL server table

Tue, 15 Mar 2011 15:53:44 GMT

Maybe I should explain a little more, the data in the table is not formatted as a cross table, it is just the raw data, each row would be something like

Name,
Status,
DateCreated,
DateCreatedMMM, /* this is the month extracted from the DateCreated */
DateClosed,
DateClosedMMM, /* this is the month extracted from the DateClosed */

So can I still manipulate data in this format this to create a Crosstable.

Is it possible to create a cross table from a SQL server table

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 16:16:36 GMT

Ok. Crosstable is one way of loading information, it's not one way of displaying, my fault.

With your data, load it normally, create a new chart, pivot table, set Name and DateCreatedMMM as dimensions, and Count(Status) as expression.

Accept and close the chart properties dialog and you will see the Names followied by a [+] sign. Expand it to see all available DateCreatedMMM for each Name. Click on the dimension (the label is fine) hold and drag to the top of the chart. You will see a vertical or horizontal blue arrow that signs the orientation of the dimension. At the top of the chart, the blue arrow should be horizonal. Then release, and that's it.

Hope that helps.

Is it possible to create a cross table from a SQL server table

Wed, 16 Mar 2011 09:27:37 GMT

Miguel

Thanks, that looks like it will do what I want...it did take me a long time to get the "hold and drop" to work.

Is it possible to create a cross table from a SQL server table

Wed, 16 Mar 2011 14:05:06 GMT

Miguel

When I had a closer look at the pivotal table I noticed that it was creating a running total rather than display the actual numbers. It doing this

Name Month Jan Feb Mar
NyName 10 20 30

Rather than

Name Month Jan Feb Mar
MyName 10 10 10

I had a look at the partial sums options but couldn't see any way to stop the table totalling the data this way.