Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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;
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;
in your expression write,
if(Code=999,'XP3',Code)
Thanks Gysbert. That was spot on