Combine multiple dimensions into one on bar graph

[chris_soto]

I am trying to sum the sales of dealers by product lines they carry, the only issue is the product lines are spread out between 3 different fields. A product line can be in the Product Line 1 field for one dealer, and in Product Line 2 field for another dealer.  I'm trying to use the Product lines as a dimension in my chart and the yearly sales as my measures. 

Here is an example layout of the data. Any dealer who has Apples as a product, I want to combine combine their sales into one bar on my chart. Any dealer who has Bananas, I want their sales to be combined into another bar. It's just to basically see how our Apples dealers are doing, how our Banana dealers are doing, etc sales wise.

[bharathadde]

Please follow below Process

Data:
LOAD * Inline
[
Dealer,Product Line 1,Product Line 2,Product Line 3,2019 Sales
Bills Store, Apples,Bananas,Carroes,1000
Joes Store, Bananas,Apples,Grapes,2000
Dereks Store, Apples, Grapes, Pretzels,3200
Jims Store, Oranges, Apples, Peaches, 1500
];

NewTable:
LOAD
Dealer,
[Product Line 1] as Product
Resident Data;
Concatenate(NewTable)

LOAD
Dealer,
[Product Line 2] as Product
Resident Data;
Concatenate(NewTable)

LOAD
Dealer,
[Product Line 3] as Product
Resident Data;

[chris_soto]

Thank you for your response, however I'm not sure this will work for me because in my live data I have thousands of dealers that are always being added and deleted, along with changing sales dollars. Is there something that would work for this?

[saurabh_karlewar]

Try data transpose using cross table load to get all the products in single column.

[jatishqv]

Hi

I think Saurabh's CrossTable approach is the best way forward.

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

Data:
LOAD
    *
FROM [lib://Data_Store/Sales_Data.xlsx]
(ooxml, embedded labels, table is Sales_Data);


Product:
CrossTable ([Product Line_Temp],[Product Line],1)
LOAD 
	Dealer,
    [Product Line 1],
    [Product Line 2],
    [Product Line 3],
    [Product Line 4]
Resident Data;


Drop Fields 
	[Product Line 1],
    [Product Line 2],
    [Product Line 3],
    [Product Line 4],
    [Product Line_Temp]
;