Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jonathanalm
New Contributor III

"Transform" columns from different data sources

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

    Unknown, 999

];

Name:

LOAD * INLINE [

  Category, Name

    1, Miller

    2, Adams

    3, Bud

];

The Table Viewer:

TableViewer.png

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:

BranchNoBranchNameCodeCategoryDevicesum(Sales)
UnknownHamburgXP33XP35000
UnknownKarlsruheXP33XP33000
0000Head Office1111XP1100
0001Tyger Valley1111XP1200
0002Klinikem2221XP1200
0003Palm Grove3332XP2400
0004Cape Gate1112XP22000
0006Big Bay2223XP31000
0007Oudehof4442XP29000
0008Sanlamhof5553XP3500

Apart from what it looks today:

BranchNoBranchNameCodeCategoryDevicesum(Sales)
UnknownHamburg9993XP35000
UnknownKarlsruhe9993XP33000
0000Head Office1111XP1100
0001Tyger Valley1111XP1200
0002Klinikem2221XP1200
0003Palm Grove3332XP2400
0004Cape Gate1112XP22000
0006Big Bay2223XP31000
0007Oudehof4442XP29000
0008Sanlamhof5553XP3500

Kind regards,

Jonathan Alm

Added QVW attachment Message was edited by: Jonathan Alm

1 Solution

Accepted Solutions

Re: "Transform" columns from different data sources

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;


talk is cheap, supply exceeds demand
3 Replies

Re: "Transform" columns from different data sources

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;


talk is cheap, supply exceeds demand
Not applicable

Re: "Transform" columns from different data sources

in your expression write,

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

jonathanalm
New Contributor III

Re: "Transform" columns from different data sources

Thanks Gysbert. That was spot on

Community Browser