topic Re: Creating a Bar Chart in QlikView

Creating a Bar Chart

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

Greetings

I have the following data (As per attached excel file)

Client has warehouse in different location (nearly 20 warehouses). all products are classified under SOFT DRINKS.

All the warehouses have the following products with different stock.

Coca-Cola Fridge Pack Cans 12 fl oz

Sprite Fridge Pack Cans 12 fl oz

Diet Coke Fridge Pack Cans 12 fl oz

Fanta Orange Fridge Pack Cans 12 fl oz

Coca Cola Cherry Coke, 12 fl Ounce

Pepsi Made with Real Sugar Cans 12 Fl Oz

Seagram's Ginger Ale Fridge Pack Cans, 12 fl oz

Coca-Cola Zero Sugar, 12 fl oz

Fresca Drink, 12 fl oz

Diet Pepsi Cans, 12 Fl Oz

Since it is test data I have duplicated the Product name. But in reality one warehouse may enter as

Coca-Cola Fridge Cans 12 fl oz

Another warehouse may enter as

Coca-Cola FridgeCans 12 FL Ounce

I need to find the total inventory for each products across all warehouse ( Coca-Cola, Sprite, Diet Coke, Fanta Orange, Coca Cola Cherry, Pepsi, Seagram's Ginger, Coca-Cola Zero Sugar, Fresca, Diet Pepsi.)

So X Axis I need Product Name, Y Axis I need Total Quantity across all warehouse.

How do I extract and create a bar chart?

Thanks

Re: Creating a Bar Chart

Lisa_P — Sun, 05 Aug 2018 23:10:02 GMT

If you are using Qlik Sense you can load your data using Data Manager. When you create a new app, select the larger option to get there. Point to your excel file or drag and drop it in.

I can't see any issues with the Product names, but you can review this as you use the Data manager.

Once loaded, it is as simple as going to the App overview/sheets in edit mode and dragging a bar chart onto the sheet and add Product Name as Dimension and QTY using a Sum aggregation as the measure to get your result.

Re: Creating a Bar Chart

Anonymous — Mon, 06 Aug 2018 02:10:14 GMT

Thanks Lisa for the Help.

There is a slight difference.

if use directly load that way , Qlik sense identify the following two as two products

Coca-Cola Fridge Cans 12 fl oz

Coca-Cola FridgeCans 12 FL Ounce

What I want is Coca-Cola to be combined into one ignoring some letters due to manual typo erros.

Basically I need to have a small additional table with selective products

Coca-Cola,

Coca Cola Cherry,

Coca-Cola Zero Sugar,

Sprite,

Diet Coke,

Fanta Orange,
Pepsi, Seagram's Ginger,

Fresca,

Diet Pepsi.

then need to extract these values and sum which I do not know how to do it.

Re: Creating a Bar Chart

qlikviewwizard — Mon, 06 Aug 2018 03:14:33 GMT

Can you show us expected results?

for

Coca-Cola Fridge Pack Cans 12 fl oz

Coca-Cola FridgeCans 12 FL Ounce

Coca-Cola FridgeCans 12 Fluid Ounce

Re: Creating a Bar Chart

pooja_prabhu_n — Mon, 06 Aug 2018 08:03:06 GMT

Hi,

create a new column for the product in the script like mentioned below.

Use Product as Dimension and Sum(QTY) as Expression.

Script:

if(WildMatch([PRODUCT NAME],'Coca-Cola*')

and WildMatch([PRODUCT NAME],'Coca-Cola ZeroSugar*')=0,'Coca-Cola',

if(WildMatch([PRODUCT NAME],'Coca Cola Cherry*'),'Coca Cola Cherry',

if(WildMatch([PRODUCT NAME],'Coca-Cola Zero Sugar*'),'Coca-Cola Zero Sugar',

if(WildMatch([PRODUCT NAME],'Sprite*'),'Sprite',

if(WildMatch([PRODUCT NAME],'Diet Coke*'),'Diet Coke',

if(WildMatch([PRODUCT NAME],'Fanta Orange*'),'Fanta Orange',

if(WildMatch([PRODUCT NAME],'Pepsi*'),'Pepsi',

if(WildMatch([PRODUCT NAME],'Seagram'&chr(39)&'s Ginger*'),'Seagram'&chr(39)&'s Ginger',

if(WildMatch([PRODUCT NAME],'Fresca*'),'Fresca',

if(WildMatch([PRODUCT NAME],'Diet Pepsi*'),'Diet Pepsi'

)))))))))) as Product,

Thanks,

Pooja

Re: Creating a Bar Chart

Or — Mon, 06 Aug 2018 08:49:37 GMT

If your list is fairly short, you can get away with using nested if() statements with Match/Wildmatch. Keep in mind that these will have to be nested in the correct order and/or with correct wildcards - otherwise, you will catch e.g. Coca-Cola Zero Sugar in the Coca-Cola filter if that one is listed first.

If your list is too long for that, you could try and get clever with string comparison, but this is pretty complicated, very slow, and I'm not sure how well it will work given you have similar product names. See String Percentage Comparison? for an example.

Note that if the superfluous words are always in that small group of "fridge", "pack", "cans", "12" etc, you could strip those characters from your strings using PurgeChar(). You'll probably have to also do some cleanup of extra whitespaces if you do that - trim() should work.