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

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

Tue, 03 Jun 2014 05:13:08 GMT

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

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Product A	1	3	3	23	23	35	35	3	35	0	8	6
Product B	5	2	42	2	4	345	53	3	8	9	80	9
Product C	5	6	23	2	24	45	3	3	35	80	80	68
Product D	2	34	234	35	2	3	5	5	0	7	80	6
Product E	4	2	24	25	53	53	3	35	4	36	80	6

Qlikview Table:

Product Code	Date	Sales
Product A	Jan	1
Product A	Feb	3
Product A	Mar	3
Product A	Apr	23
...	...	...
Product A	Dec	6
Product B	Jan	5
Product B	Feb	2
...	...	...
Product B	Dec	9
...

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

ashfaq_haseeb — Tue, 03 Jun 2014 06:00:29 GMT

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)

its_anandrjs — Tue, 03 Jun 2014 06:09:39 GMT

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

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

amit_saini — Tue, 03 Jun 2014 06:14:36 GMT

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:

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,

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

Tue, 03 Jun 2014 06:24:06 GMT

Hi Ashfaq:

Thank you very much.

Cheers,

Paco