3 Replies Latest reply: Apr 25, 2013 2:41 AM by Jonathan Alm RSS

    "Transform" columns from different data sources

    Jonathan Alm

      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