topic Re: "Transform" columns from different data sources in QlikView

"Transform" columns from different data sources

Jonathan_Alm — Wed, 24 Apr 2013 12:36:54 GMT

Hi,

I have a problem that I need some advice on.

I have three datasources that are connected through the Main table.

Main:

LOAD * INLINE [

BranchNo, BranchName, Sales, Category

0000, Head Office, 100,1

0001, Tyger Valley, 200,1

0002, Klinikem, 200,1

0003, Palm Grove, 400,2

0004, Cape Gate, 2000,2

0007, Oudehof, 9000,2

0008, Sanlamhof, 500,3

0006, Big Bay, 1000,3

Unknown, Karlsruhe, 3000,3

Unknown, Hamburg, 5000,3

];

Code:

LOAD * INLINE [

BranchNo, Code

0000, 111

0001, 111

0002, 222

0003, 333

0004, 111

0007, 444

0008, 555

0006, 222

Unknown, 999

];

Name:

LOAD * INLINE [

Category, Name

1, Miller

2, Adams

3, Bud

];

The Table Viewer:

In an ideal situation, I'd like to use the "Enable Transformation Step" that you can use when you import a txt/qvd file. But since the columns I want to transform are scattered in different tables this cannot be used. (I think)

In the example above, I want to fill the Column "Code" with the Column "Device" when the Code = 999.

So in my Pivot-table I want the following to be displayed:

BranchNo	BranchName	Code	Category	Device	sum(Sales)
Unknown	Hamburg	XP3	3	XP3	5000
Unknown	Karlsruhe	XP3	3	XP3	3000
0000	Head Office	111	1	XP1	100
0001	Tyger Valley	111	1	XP1	200
0002	Klinikem	222	1	XP1	200
0003	Palm Grove	333	2	XP2	400
0004	Cape Gate	111	2	XP2	2000
0006	Big Bay	222	3	XP3	1000
0007	Oudehof	444	2	XP2	9000
0008	Sanlamhof	555	3	XP3	500

Apart from what it looks today:

BranchNo	BranchName	Code	Category	Device	sum(Sales)
Unknown	Hamburg	999	3	XP3	5000
Unknown	Karlsruhe	999	3	XP3	3000
0000	Head Office	111	1	XP1	100
0001	Tyger Valley	111	1	XP1	200
0002	Klinikem	222	1	XP1	200
0003	Palm Grove	333	2	XP2	400
0004	Cape Gate	111	2	XP2	2000
0006	Big Bay	222	3	XP3	1000
0007	Oudehof	444	2	XP2	9000
0008	Sanlamhof	555	3	XP3	500

Kind regards,

Jonathan Alm

Added QVW attachment Message was edited by: Jonathan Alm

Re: "Transform" columns from different data sources

Gysbert_Wassenaar — Wed, 24 Apr 2013 13:16:41 GMT

Something like this perhaps:

CodeMap:

MAPPING LOAD * INLINE [

BranchNo, Code

0000, 111

0001, 111

0002, 222

0003, 333

0004, 111

0007, 444

0008, 555

0006, 222

Unknown, 999

];

Temp:

LOAD * INLINE [

Category, Name, Device

1, Miller, XP1

2, Adams, XP2

3, Bud, XP3

];

join

LOAD *, applymap('CodeMap',BranchNo,'999') as Code

INLINE [

BranchNo, BranchName, Sales, Category

0000, Head Office, 100,1

0001, Tyger Valley, 200,1

0002, Klinikem, 200,1

0003, Palm Grove, 400,2

0004, Cape Gate, 2000,2

0007, Oudehof, 9000,2

0008, Sanlamhof, 500,3

0006, Big Bay, 1000,3

Unknown, Karlsruhe, 3000,3

Unknown, Hamburg, 5000,3

];

Result:

noconcatenate

Load

BranchNo, BranchName, Sales, Category,

if(Code='999',Device,Code) as Code

resident Temp;

drop Table Temp;

Re: "Transform" columns from different data sources

Wed, 24 Apr 2013 13:32:48 GMT

in your expression write,

if(Code=999,'XP3',Code)

Re: "Transform" columns from different data sources

Jonathan_Alm — Thu, 25 Apr 2013 06:41:16 GMT

Thanks Gysbert. That was spot on