Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)))
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 ?
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
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.
Can you share the script that you have?
//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;
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.
I was asking for the script... and not the data that you might have
I got that. The information in the script is also confidential.