11 Replies Latest reply: Dec 11, 2017 1:26 PM by ra ta RSS

    Stacking 4 columns

    ra ta

      Hi, in the load editor I am loading and reading a table from the database that looks like this:

          

      Country1Country2    Sales1    Sales2
      UKJapan1001000
      GermanyFrance1101100
      FranceMalaysia1201200
      ItalyGermany1301300

       

      Where Sales1 is for Country1, and Sales2 is for country2.

       

      I would like to display the above data in a chart of Sales by Country using a table similar to this:

       

      CountrySales (Sales1 + Sales2)
      UK100
      Germany1410
      France1220
      Italy130
      Japan1000
      Malaysia1200

       

      Where I sum Sales by country.

       

      Could you please suggest a solution to this. Thanks very much.

       

      Regards,

      Rachid

        • Re: Stacking 4 columns
          Sunny Talwar

          May be like this

           

          Table:

          LOAD RowNo() as UniqueIdentifier,

          *;

          LOAD * INLINE [

              Country1, Country2,    Sales1,    Sales2

              UK, Japan, 100, 1000

              Germany, France, 110, 1100

              France, Malaysia, 120, 1200

              Italy, Germany, 130, 1300

          ];


          LinkTable:

          LOAD Distinct Country1 as Country,

          UniqueIdentifier

          Resident Table;


          Concatenate(LinkTable)

          LOAD Distinct Country2 as Country,

          UniqueIdentifier

          Resident Table;

           

          Dimension

          Country

           

          Expression

          =Sum(RangeSum(If(Country = Country1, Sales1), If(Country = Country2, Sales2)))

           

          Capture.PNG

            • Re: Stacking 4 columns
              ra ta

              Thanks for the follow up. I suppose the load * inline goes in the load editor. I am not sure that will work because my data keep changing and i would like to read it from the BD, not hard code it in the editor as you suggested above ?

                • Re: Stacking 4 columns
                  Sunny Talwar

                  No Inline is needed... this was done to just demonstrate what you need to do... if you have Excel as data source or someother database, you won't need the Inline load

                    • Re: Stacking 4 columns
                      ra ta

                      ok, thanks. so if i understood correctly, i should start from LinkTable, which i have tried but i got an error saying

                      Field 'UniqueIdentifier' not found

                       

                      I suppose this is because I am loading the data without specifying LOAD RowNo() as UniqueIdentifier. What does this do exactly and is there any other way?

                      The table I am loading has so much more than country and sales in it, and for this question, I only need those 4 columns. Thank you.                                                                                                                                                

                • Re: Stacking 4 columns
                  Diego Barboza

                  //This inline table should be replaced with your datasource table

                  Original_Datasource:

                  LOAD * INLINE [

                      Country1, Country2,    Sales1,    Sales2

                      UK, Japan, 100, 1000

                      Germany, France, 110, 1100

                      France, Malaysia, 120, 1200

                      Italy, Germany, 130, 1300

                  ];

                   

                  //Temp table with the first data concatenation

                  Temp_Table:

                  LOAD Country1 AS COUNTRY,

                       Sales1 AS SALES,   

                  RESIDENT Original_Datasource;

                  CONCATENATE (Main_table)

                  LOAD Country2 AS COUNTRY,

                       Sales2 AS SALES,   

                  RESIDENT Original_Datasource;

                   

                  DROP TABLE Original_Datasource;

                  NoConcatenate

                   

                  //Definitive table 

                  Main_Table:

                  LOAD COUNTRY,

                       SUM(SALES) AS SALES

                  RESIDENT Temp_Table;

                   

                  DROP TABLE Temp_Table;