Stacking 4 columns

[ts3st1000]

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

Country1	Country2	Sales1	Sales2
UK	Japan	100	1000
Germany	France	110	1100
France	Malaysia	120	1200
Italy	Germany	130	1300

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:

Country	Sales (Sales1 + Sales2)
UK	100
Germany	1410
France	1220
Italy	130
Japan	1000
Malaysia	1200

Where I sum Sales by country.

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

Regards,

Rachid

[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)))

[ts3st1000]

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 ?

[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

[ts3st1000]

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.                                                                                                                                                

[sunny_talwar]

Can you share the script that you have?

[diego_a_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;

[ts3st1000]

Thanks but unfortunately I can not share client data.

I have finally used the following which did not give me any errors on the loader:

LinkTable:

LOAD Distinct Country1 as Country,

Resident Table;

Concatenate(LinkTable)

LOAD Distinct Country2 as Country,

Resident Table;

Without the unique identifier, and it seems to work.

Question on the side. Some of the values show 0 (zero). Any idea how I can hide those please, ie show only countries where Sales is non-zero. Thank you.

[sunny_talwar]

I was asking for the script... and not the data that you might have

[ts3st1000]

I got that. The information in the script is also confidential.