Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading A Two Dimension Excel File (with a dimension on the top)

Hi:

I am wondering is it possible to transfer a two dimension Excel file with one dimension on the top to a two dimension table with all dimensions on the left when loading in the script.

Thank you very much.

Here is an example

Excel File

JanFebMarAprMayJunJulAugSepOctNovDec
Product A13323233535335086
Product B52422434553389809
Product C5623224453335808068
Product D23423435235507806
Product E4224255353335436806

Qlikview Table:

Product CodeDateSales
Product A

Jan

1
Product AFeb3
Product AMar3
Product AApr23
.........
Product ADec6
Product BJan5
Product BFeb2
.........
Product BDec9
...
Tags (3)
1 Solution

Accepted Solutions

Re: Loading A Two Dimension Excel File (with a dimension on the top)

Hi use cross table to do that.

may be like attached.

Regards

ASHFAQ

4 Replies

Re: Loading A Two Dimension Excel File (with a dimension on the top)

Hi use cross table to do that.

may be like attached.

Regards

ASHFAQ

Re: Loading A Two Dimension Excel File (with a dimension on the top)

Try with the below load script and load your table as cross table.

TableName:

CrossTable(Months, Data)

LOAD Product,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec

FROM

Cross.xlsx

(ooxml, embedded labels, table is Sheet1);

amit_saini
Honored Contributor III

Re: Loading A Two Dimension Excel File (with a dimension on the top)

Hi Baikang,

This can be achieved by Cross Table concept, Please see below to understand the concept.

A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. It could look like the table below:

Example:

  1. a.csv

Year

Jan

Feb

Mar

Apr

May

1991

45

65

78

12

78

1992

11

23

22

22

45

1993

65

56

22

79

12

1994

65

24

32

78

55

1995

45

56

35

78

68

If this table is simply loaded into QlikView, the result will be one field for Year and one field for each of the months. This is generally not what you would like to have. One would probably prefer to have three fields generated, one for each header category (Year and Month) and one for the data values inside the matrix.

This can be achieved by adding the crosstable prefix to your load or select statement.The statement for loading this cross table could be:

crosstable (Month, Sales) load * from a.csv;

The result in QlikView would be as follows:

Year

Month

Sales

1991

Jan

45

1992

Feb

11

1993

Mar

65

1994

Apr

65

1995

May

65

23

56

24

Thanks,

AS

Not applicable

Re: Loading A Two Dimension Excel File (with a dimension on the top)

Hi Ashfaq:

Thank you very much.

Cheers,

Paco

Community Browser