Automating Excel Cross Table Loads?

[#1]

Hello Everyone-

Loving Qlikview and pleased with the amazing community this product brings.  That being said, I'm extremely new to Qlikview as well as data loading in general. I've been working on my first project with QV, and I've easily learned - through the use of QV's awesome wizard tool - how to load in a cross table excel file into QV.  I've been going about making charts, tables, etc.  That being said, I'm wondering how to handle any future updates in terms of data?

The file is structured thusly:

Product, Brand, Jan-2010, Feb-2010, Mar-2010....

Milk, Joes Milk, 2000, 4000, 5000....

Cheese, New England Cheese, 3000, 4000, 5000

Obviously, when I loaded it into QV, I converted the excel file where the Jan-2010, Feb-2010...  were made as Date, with the values being Cost.

My newbie problem is how can I automate this transformation and loading into QV?  Each month, my excel file will be structured the same, but with a new month.  Any advice would be greatly appreciated!

[chriscammers]

It is actually easier than it would seem. When you are loading your spreadsheet you can load all fields by using the * in the load, then perform your crosstable conversion just like normal.

As the different months are added they will be automatically included.

Budget:   

Crosstable(Month, Sales, 1)

LOAD *

FROM

CrossTableSample.xlsx

(ooxml, embedded labels, table is Sheet1);

[#3]

Hi,

Welcome to Qlikview and QlikCommunity .

Take for example, my transaction table look like below.

LOAD * INLINE [

    Region, Jan 2010, Feb 2010, Mar 2010, Apr 2010

    A, 1, 4, 5, 6

    B, 2, 9, 8, 7

    C, 3, 2, 3, 4

];

I want this to be look like the below.

Region	Month	Value
A	Apr 2010	6
A	Feb 2010	4
A	Jan 2010	1
A	Mar 2010	5
B	Apr 2010	7
B	Feb 2010	9
B	Jan 2010	2
B	Mar 2010	8
C	Apr 2010	4
C	Feb 2010	2
C	Jan 2010	3
C	Mar 2010	3

My code would look like below..

CrossTable(Month, Value,1)

LOAD * INLINE [

    Region, Jan 2010, Feb 2010, Mar 2010, Apr 2010

    A, 1, 4, 5, 6

    B, 2, 9, 8, 7

    C, 3, 2, 3, 4

];

Here CrossTable is the key word.

1st Parameter (Month) - Tells Qlikview that my cross table fields names has to load into a new field called Month.

2nd Parameter (Value) - Tells Qlikview that my Cross table field values to be loaded under the New field Value

3rd Parameter (1) - Tells Qlikview that how many Qualifying fields i.e. how may fields to be skipped/not to be affected by cross table.

Have look at the attached application.

Hope this may helps you.

- Sridhar

[#4]

chriscammers-

Thanks so much for this. Extremely helpful! I hope no one here minds if I expand this further?

How would I handle if instead every monthly the user would drop in the excel file each month into some directory with the new additions as an (hope I have this terminology correct) incremental load?

Awesome stuff everyone.

[chriscammers]

Do you mean that there would be multiple spreadsheets, like one for each year??

If so it is also relatively easy...

Just like before except now you'll notice the asterix in the name of the excel file. The asterix is a wildcard which will cause Qlikview to grab all the workbooks whose names match the pattern. With this you'd get sheets with a variety of names like these

CrossTableSample_2010.xlsx or CrossTableSampleCurrent.xlsx or CrossTableSample_BlahBlah.xlsx

Budget:   

Crosstable(Month, Sales, 1)

LOAD *

FROM

CrossTableSample*.xlsx

(ooxml, embedded labels, table is Sheet1);

[#6]

chriscammers-

This is AWESOME.  Thanks so much. Really great stuff!

[#7]

Hi all,

Thanks for the insight with the crosstables so far, very helpful.

I have a further hurdle in this same problem:

I have an excel sheet that is laid out as follows:

                       Product A                                             Product B

Region     Jan 2012  Feb 2012 Mar 2012        Jan 2012  Feb 2012  Mar 2012

A                    1          5             9                              13          17             21

B                    2          6             10                             14          18             22

C                    3          7             11                             15          19             23

D                    4          8             12                             16          20             24

It seems that crosstable implementation into QV works well if there is only one attribute field, but in this case I have two (Products and Months).  Is there any work around for this?

Thanks in advance.

[gussfish]

Yes, but it gets tricky!  In outline, you need to break-up your loading into two steps and two tables:

1. a CROSSTABLE load of the data without the headings (with the result that @2, @3, @4 are used as the column headings and hence end-up in your attribute field (which I'll call ColumnId).  That's the easy bit.
2. load in the values in the headings, transpose them, and use RecNo() to generate their corresponding attribute field values.  This requires some File Wizard Transformation Step tricky: specify No Labels and No heading rows and then go into "Enable Transformation Step".  In here
   1. use Conditional Delete to delete all rows from 3 to the end
   2. delete the Region column
   3. on the Rotate tab, do a Transpose.  Now, your Product heading row has become a Product column, but it has lots of blanks in it.
   4. use the Fill tab to fill in the blanks
   5. Finish the Wizard to generate the LOAD statement.  Insert the LOAD statement a ColumnId field constructed from RecNo().

My resulting code looks like this:

Data:

CROSSTABLE (ColumnId,Value,1)

LOAD @1 AS Region,

     @2, @3, @4, @5, @6, @7

FROM sales.xls (biff, no labels, header is 2 lines, table is Sheet1$);

Headings:

LOAD

    '@'&(RecNo()+1) AS ColumnId,

    @1 AS Product,

    @2 AS Month

FROM c:\temp\sales.xls (biff, no labels, table is Sheet1$, filters( Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Bottom, 1), Select(1, 0))), Transpose(), Transpose(), Remove(Col, Pos(Top, 1)), Transpose(), Replace(1, top, StrCnd(null)) ));