Multiple table's data load

[qlikviewwizard]

Hi All,

How to load the xls data in crosstab load as a one table.

Please advise.

Thanks in advance.

Data:

SALES						MARKETING						ACCOUNTING
Year	SALARY	10	20	30		Year	JOB	10	20	30		Year	JOB	10	20	30
2015	3,600	1,200	1,200	1,200		2014	Analyst	1,230	1,230	1,230		2015	Analyst	1,200	1,200	1,200
2015	6,000	2,000	2,000	2,000		2014	Sr Analyst	1,600	1,600	1,600		2015	Sr Analyst	2,000	2,000	2,000
2015	13,680	4,560	4,560	4,560		2014	MANAGER	4,560	4,560	4,560		2015	MANAGER	4,560	4,560	4,560

[#2]

Hi,

Can you provide your sample xls , you need to make use of transformation step may be if both sales & marketing are in same xls.

[qlikviewwizard]

Attached.

[sunilkumarqv]

Firstly use  join

sales table with Marketing table then use cross table

crosstable(Field,value,3)

[qlikviewwizard]

Could you please provide the code in QVW file?

[anbu1984]

LOAD 'Sales' As Tbl,

  Year,

    SALARY,

    [10],

    [20],

    [30]

FROM

[Book1 - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is EmpData)

Where Len(Trim(Year)) > 0;

LOAD 'Marketing' As Tbl,

    Year1 As Year,

    JOB As SALARY,

    [101] AS [10],

    [201] AS [20],

    [301] AS [30]

FROM

[Book1 - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is EmpData)

Where Len(Trim(Year1)) > 0;

LOAD 'Accounting' As Tbl,

    Year2 As Year,

    JOB1 As SALARY,

    [102] AS [10],

    [202] AS [20],

    [302] AS [30]

FROM

[Book1 - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is EmpData)

Where Len(Trim(Year2)) > 0;

[qlikviewwizard]

Hi anbu1984,

Thanks for the file.

How to achive the same for crosstab table. 10,20,30 are to be new column as DEPTNO

[anbu1984 · Accepted solution]

CrossTable (DeptNo,SALARY,4)

LOAD 'Sales' As Tbl,

  Year,

     SALARY aS JOB,

     [10],

     [20],

     [30]

FROM

[Book1 - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is EmpData)

Where Len(Trim(Year)) > 0;

CrossTable (DeptNo,SALARY,4)

LOAD 'Marketing' As Tbl,

     Year1 As Year,

     JOB,

     [101] AS [10],

     [201] AS [20],

     [301] AS [30]

FROM

[Book1 - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is EmpData)

Where Len(Trim(Year1)) > 0;

CrossTable (DeptNo,SALARY,4)

LOAD 'Accounting' As Tbl,

     Year2 As Year,

     JOB1 As JOB,

     [102] AS [10],

     [202] AS [20],

     [302] AS [30]

FROM

[Book1 - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is EmpData)

Where Len(Trim(Year2)) > 0;