Creating a Bar Chart

[Anonymous]

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

[Lisa_P]

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.

[Anonymous]

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.

[qlikviewwizard]

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

[pooja_prabhu_n]

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

[Or]

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.