-
Re: Stacking 4 columns
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)))
-
284661.qvw 154.5 K
-
Re: Stacking 4 columns
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 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 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
Sunny Talwar Dec 11, 2017 8:29 AM (in response to ra ta)Can you share the script that you have?
-
Re: Stacking 4 columns
ra ta Dec 11, 2017 9:10 AM (in response to Sunny Talwar )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.
-
Re: Stacking 4 columns
Sunny Talwar Dec 11, 2017 9:36 AM (in response to ra ta)I was asking for the script... and not the data that you might have
-
Re: Stacking 4 columns
ra ta Dec 11, 2017 10:04 AM (in response to Sunny Talwar )I got that. The information in the script is also confidential.
-
-
-
Re: Stacking 4 columns
ra ta Dec 11, 2017 1:26 PM (in response to ra ta)I have just noticed a new table called LinkTable in the Data model viewer that is not connected to anything... do you think that could cause any issues. I already have few tables and they are all connected. thanks.
-
-
-
-
-
-
-
Re: Stacking 4 columns
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;