topic Re: Multiple table's data load in QlikView

Multiple table's data load

qlikviewwizard — Thu, 26 Mar 2015 05:43:22 GMT

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

Re: Multiple table's data load

Thu, 26 Mar 2015 06:04:08 GMT

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.

Re: Multiple table's data load

qlikviewwizard — Thu, 26 Mar 2015 06:19:07 GMT

Attached.

Re: Multiple table's data load

sunilkumarqv — Thu, 26 Mar 2015 06:32:50 GMT

Firstly use join

sales table with Marketing table then use cross table

crosstable(Field,value,3)

Re: Multiple table's data load

qlikviewwizard — Thu, 26 Mar 2015 06:46:09 GMT

Could you please provide the code in QVW file?

Re: Multiple table's data load

anbu1984 — Thu, 26 Mar 2015 07:06:32 GMT

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;

Re: Multiple table's data load

qlikviewwizard — Thu, 26 Mar 2015 07:18:01 GMT

Hi anbu1984,

Thanks for the file.

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

Re: Multiple table's data load

anbu1984 — Thu, 26 Mar 2015 07:36:17 GMT

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;