Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Month | Colour | Count |
Jan | Red | 1 |
Feb | Yellow | 2 |
Mar | Blue | 3 |
Jan | Red | 4 |
Feb | Yellow | 5 |
Mar | Blue | 6 |
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
Month | Colour | Count |
Jan | Red | 5 |
Feb | Yellow | 7 |
Mar | Blue | 9 |
Or even better If I could through a cross table, get the LOAD to look like this:
Jan | Feb | Mar | |
Red | 5 | 0 | 0 |
Yellow | 0 | 7 | 0 |
Blue | 0 | 0 | 9 |
Is any kind enough to help me with this....I have spent hours trying all different types of qualifies fields etc
Kind Regards
Helen
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
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
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
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