Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Loading Cross Tables

Employee
Employee

Loading Cross Tables

This videos show how to load a cross table in to QlikView.  A cross table has category information along the rows and columns.  QlikView, like most analytical systems needs the data uncrossed to be most effective.

If you are unsure how to load data into QlikView please see:

Loading ODBC Data into QlikView

Loading Excel Data into QlikView

This is an example of a cross table:

Sales PersonQ1Q2Q3Q4

John

50302010
Mary30105010

During the load the data will be transformed to this format:

Sales PersonQuarterSales
John

Q1

50

Mary

Q130
JohnQ230
MaryQ210
JohnQ320
MaryQ350
JohnQ410
MaryQ410
Attachments
Comments
carbal1952
Contributor II

Always a video would be much better and helpful if a related QVW file would be attached. Thank you.

Employee
Employee

Thanks for the suggested.  I have updated the post with the data and qvw files.

Not applicable

Great example, thank you.

Not applicable

very helpful information. Thank you!

Emmanuelle-Bustos
Valued Contributor

Thanks!!

Not applicable

hi josh/(anyone else who can help),

A quick question.  If my source file gets updated with new columns being added every week like, Q5 this week, Q6 the week after that etc, how does crosstable work in that scenario? I realized that even after a reload on the script, the data from the new columns somehow do not seem to appear. I am not sure if this is limitation of the crosstable functionality or just something i am doing wrong.

thanks.

Employee
Employee

You need to change the script so it has the new columns included.  You could do this manually or use a script like the one below which generates the script based on a start and end date.  Note this script assume you are using data in the same format as the sample data above.

//Input Start and End Dates Here (or make this dynamic)

Set vStartDate = Date#('1/1/2010','DD/MM/YYYY');

Set vEndDate = Date#('12/31/2012','MM/DD/YYYY');

//Mapping for Quarters - this assumes the you have your data by quarter

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

                LOAD Distinct

                             1 as Grouper,

                             ApplyMap('QuartersMap', month($(vStartDate) + IterNo() - 1)) & ' ' & Year($(vStartDate) + IterNo() - 1) as QuartersList

                               AutoGenerate 1 While $(vStartDate) + IterNo() -1 <= $(vEndDate);

Temp2:

Load '[' & Concat(QuartersList,'],[') & ']'  As QuartersLoadList

Resident Temp

Group by Grouper;

//Create a variable that has the correct text string for the script below

Let vQuartersLoadList = Peek('QuartersLoadList', 0, 'Temp2');;

CrossTable(Quarter, Sales)

LOAD [Sales Rep],

$(vQuartersLoadList)

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(contain, 'Total'))),

Replace(1, top, StrCnd(null))

));

Drop Tables Temp, Temp2;

Not applicable

associated search did not work.

Luminary
Luminary

Thanks Josh. One more video on the same topic.

http://qlikshare.com/qlikview-video-tutorial-crosstable-qlikview-transpose-data/

Thanks,

DV

Not applicable

Gosti

Version history
Revision #:
1 of 1
Last update:
‎09-30-2013 06:55 PM
Updated by:
Employee