Discussion board where members can get started with Qlik Sense.
Hi, in the load editor I am loading and reading a table from the database that looks like this:
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:
Where I sum Sales by country.
Could you please suggest a solution to this. Thanks very much.
May be like this
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
LOAD Distinct Country1 as Country,
LOAD Distinct Country2 as Country,
=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
//Temp table with the first data concatenation
LOAD Country1 AS COUNTRY,
Sales1 AS SALES,
LOAD Country2 AS COUNTRY,
Sales2 AS SALES,
DROP TABLE Original_Datasource;
SUM(SALES) AS SALES
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:
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.