Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

helen_pip
Contributor II

Cross table dilemma

Hello

I was wondering if someone could help em write the script to get a straight forward Load from Excel into a cross table format

I am using a straight forward example, and am looking for help with cross table techniques, as my final LOAD will be more complicated, and this is the technique I know I need to use going forward

My initial LOAD will be in this format:

MonthColourCount
JanRed1
FebYellow2
MarBlue3
JanRed4
FebYellow5
MarBlue6

which I will then store as a qvd file

I need help to write the script to get the load into this cross table format and the data to be in this format

MonthColourCount
JanRed5
FebYellow7
MarBlue9

Or even better If I could through a cross table, get the LOAD to look like this:

JanFebMar
Red500
Yellow070
Blue009

Is any kind enough to help me with this....I have spent hours trying all different types of qualifies fields etc

Kind Regards

Helen

4 Replies
Highlighted
Not applicable

Re: Cross table dilemma

Hi Helen,

one great advantage of Qlikview is that you don't need to predefine and load any cubes or(and) cross tables. Just load the data and build the cross tables using new object --> charts --> pivot table --> dimensions are month and color, expression is sum(count).

Just take a look at my example application.

HtH

Rolandf

helen_pip
Contributor II

Re: Cross table dilemma

Hello

Thank you for your response.  I think I will find that tecnique useful in the future

However the example I posted above is just to learn the technique of buliding from cross tables because my final and real LOAD will be a long piece of SQL which has many sub headings which I cannot reproduce through a pivot table and I need to restructure my data first in the edit script

My ultimate aim is to produce a scorecard type table with many subheadings.....Hence the need to cross table

If you are able to help me with how I can get to a cross table situation using the example I posted above, any help would be greatly appreciated

Kind Regards

Helen

Not applicable

Re: Cross table dilemma

OK,

to achieve the first table, load it similar to a SQL group by:

RawData:

LOAD Month, Colour, Sum(Count) AS Count Inline [

Month,Colour,Count

Jan,Red,1

Feb,Yellow,2

Mar,Blue,3

Jan,Red,4

Feb,Yellow,5

Mar,Blue,6

]

Group by Month, Colour

;

To build crosstables like your second example I would always use the design utilities as mentioned in my post above.

May be any one else has some ideas ?

Roland

MVP
MVP

Re: Cross table dilemma

If you want to create the crosstable fields in your script, you can use GENERIC LOAD prefix for this.

Check out the HELP and Rob's blog which explains how to do this, including some samples:

http://qlikviewnotes.blogspot.co.il/2010/05/use-cases-for-generic-load.html

Regards,

Stefan