Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ts3st1000
New Contributor

Stacking 4 columns

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

11 Replies

Re: Stacking 4 columns

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

ts3st1000
New Contributor

Re: Stacking 4 columns

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

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
New Contributor

Re: Stacking 4 columns

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

Can you share the script that you have?

diego_a_barboza
New Contributor III

Re: Stacking 4 columns

//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
New Contributor

Re: Stacking 4 columns

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

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

ts3st1000
New Contributor

Re: Stacking 4 columns

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

Community Browser