Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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
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.