topic Re: Automating Excel Cross Table Loads? in QlikView

Automating Excel Cross Table Loads?

Fri, 17 Jun 2011 12:02:20 GMT

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!

Automating Excel Cross Table Loads?

chriscammers — Fri, 17 Jun 2011 12:21:11 GMT

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);

Re: Automating Excel Cross Table Loads?

Fri, 17 Jun 2011 12:27:28 GMT

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

Automating Excel Cross Table Loads?

Fri, 17 Jun 2011 12:58:31 GMT

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.

Automating Excel Cross Table Loads?

chriscammers — Fri, 17 Jun 2011 13:15:31 GMT

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);

Automating Excel Cross Table Loads?

Fri, 17 Jun 2011 18:28:11 GMT

chriscammers-

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

Re: Automating Excel Cross Table Loads?

Wed, 01 Feb 2012 18:38:46 GMT

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.

Re: Automating Excel Cross Table Loads?

gussfish — Wed, 01 Feb 2012 22:47:23 GMT

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

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.
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)) ));