Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
... |
Hi use cross table to do that.
may be like attached.
Regards
ASHFAQ
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);
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:
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
Hi Ashfaq:
Thank you very much.
Cheers,
Paco