Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Alm
Partner - Creator
Partner - Creator

"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
Gysbert_Wassenaar

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

View solution in original post

3 Replies
Gysbert_Wassenaar

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

in your expression write,

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

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Thanks Gysbert. That was spot on