Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

NameJanFebMarAprMayJunJulAugSepOctNovDec
Name120910000000000
Name2251020000000000
Name 2301130000000000
Monthly Totals753060000000000
Yearly Total165


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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

5 Replies
Miguel_Angel_Baeyens

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.

Not applicable
Author

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.

Miguel_Angel_Baeyens

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.

Not applicable
Author

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.

Not applicable
Author

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.