4 Replies Latest reply: Jan 18, 2016 6:50 AM by Joan Cardona RSS

    How to convert columns to rows in the data load editor?

    Joan Cardona

      Hello everyone,

       

      I'm currently loading a table from Excel with the following format (each row corresponds to a project, say project 1, 2, 3 and 4:

       

      ProjectIncome AIncome BIncome CIncome D
      14331
      25672
      37121
      42582

       

      To load it, I use:

       

      Taula:

      Load

          Project,

          "Income A",

          "Income B",

          "Income C",

          "Income D",

      From [lib://data.xlsx]

      (ooxml, embedded labels, table is [Project Revenues]);

       

      So, until here I haven't had any issues, it's pretty basic stuff. The thing is that what I need is a table with the following format (to make a waterfall chart), getting data from the previous table (using resident):

       

      Income TypeAmountOffsetMultiplier
      ASum(Income A) = 4 + 5+ 7+ 2 = 1801
      BSum(Income B) = 3 +6 +1+  5 = 151-1
      CSum(Income C) = 3 + 7 + 2 + 8 = 2011
      DSum(Income D) = 1 + 2 + 1 + 2 = 61-1

       

      My best idea so far has been to load an inline table as such:

       

      LOAD * INLINE [

      Income Type, Offset, Multiplier

      A, 0, 1

      B, 1, -1

      C, 1, 1

      D, 1, -1

      ];

       

      And then trying to concatenate a column containing the sums of each type of income, but have been unsuccessfull so far. This is what I've come up with, even though it doesn't do what I want it to do:

       

      aux:

      Load

          SUM("A")

          SUM("B")

          Sum("C")

          Sum("D")

      RESIDENT Taula;

       

      And trying to concatenate this to the other inline table.

       

      Can anyone give me a hand?

       

      Thank you very much,

       

      JC