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

    Stacking 4 columns

    ra ta
    ra ta Dec 11, 2017 7:33 AM

      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

      • 130 Views
      • Tags:
        • Re: Stacking 4 columns
          Sunny Talwar
          Sunny Talwar Dec 11, 2017 7:40 AM (in response to ra ta)

          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
              ra ta Dec 11, 2017 7:58 AM (in response to Sunny Talwar )

              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
                  Sunny Talwar Dec 11, 2017 8:00 AM (in response to ra ta)

                  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
                      ra ta Dec 11, 2017 8:25 AM (in response to Sunny Talwar )

                      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
                  Diego Barboza Dec 11, 2017 9:03 AM (in response to ra ta)

                  //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;