topic Re: Stacked bar and Joining two tables causing duplicates in QlikView

Stacked bar and Joining two tables causing duplicates

haribabugv — Wed, 25 Nov 2020 16:16:04 GMT

Hello Qlikview gurus,

My issue here is

I have two tables in excel as attached- i want to join these two tables and create new without causing duplicates.

Once two tables have been joined, i need to create stacked bar chart and line with that new table.

I have attached a qlikview workbook where I have created stacked bar chart and line chart(made it transparent) so that i can have both the charts in one place.

Is there any solution on how to combine two tables into one table and end up having a stacked bar chart and line chart as attached in the qlikview workbook.

Please note: this is sample data.I have n number of customers and n number of products-so set analysis is ruled out

Re: Stacked bar and Joining two tables causing duplicates

rubenmarin — Sun, 01 Oct 2017 18:17:59 GMT

Hi Hari, you can use ApplyMap to avoid duplicates, first you create the applymap (a LOAD with a'Mapping' prefix):

mapRate:

Mapping LOAD Customer,

Rate%

FROM ...

;

Second, use this map in ApplyMap function, it searchs the value in the first field of the map and retuns the second field value. Also has an optional 3rd parameter to set a value when the value is not found (by default if the value is not found in map it returns the searched value:

Data:

LOAD Customer,

Product,

Sales,

ApplyMap('mapRate', Customer, Null()) as Rate%

FROM ...

;

Mapping tables dissapears when the load has finished, so it doesn't have any impact on data model.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/MappingFunctions/ApplyMap.htm

Re: Stacked bar and Joining two tables causing duplicates

haribabugv — Sun, 01 Oct 2017 19:29:47 GMT

Thanks Ruben.

I am now able to avoid duplicates.

How can we use stacked bar and line chart for the attached qlikview workbook?

'Product' dimension in the this chart should be shown as stacked bar and x axis should be customer?

any solution?

Re: Stacked bar and Joining two tables causing duplicates

rubenmarin — Mon, 02 Oct 2017 07:06:59 GMT

Sorry, I don't how to avoid the use of transparent chart in this case, If there was only one dimension, or if this dimension has a limited set of values you could add a bar for each dimension value, this way stacked bars can be mixed with lines.

With this data, both 'Customer' and 'Product' will have many different values to try this approach, I attach a sample so you can check (as I said, I don't think you can use this with real data- there will be many diffrent products)