Is there anyway to count them the way we want...?

Wed, 29 Oct 2014 21:21:37 GMT

This application is so tough for me...Thanks to Tobias Klett, I successfully linked Table1 and Table2 together a few weeks ago. I couldn't have gone this far without Tobias's help.

Now, I need to go one step further to count the quantity in a unique way (see below)

Here is what I've got.

In Table1, I have a ProductDescription column. The first several letters of each ProductDescription indicates the ProductName. Eg. The ProductName of “Pandacookielover” is “Panda”. The ProductName of “Tomatorocks” is “Tomato”. The ProductDescriptions that are not able to find any correspondent ProductNames are named "Other".

Here is what I did:

1. Read the ProductNames into a mapping table

Map_ProductNames:

Mapping Load distinct

ProductName as Inputfield,

ProductName as ResultFieldResident Table2;

2. linked the tables

Used an if for each step like this:if( len(applymap('Map_ProductNames',left(ProductDescription,10),''))>0 // Condition , applymap('Map_ProductNames',left(ProcuctDescription,10),'') // true -> take , if( len .......same with 9,8,7 // else -> (I wrote 'Others' in the last "Else" instead of using number 1 to specify the ProductDescptions that are not able to find the correspondent ProductNames)

HERE IS THE QUESTION FOR TODAY:

The above solution worked great until yesterday, our company wanted to count any ProductDescription with "-trim-" or "-box-" half.

eg. if there is "Pandacookielover-box-***" with Qty of 4, we should count it as 4/2=2 instead of 4. Other ProductDescriptions without "-trim-" or "-box-" should just count as is. What should I do, folks...? Any input is appreciated!

In table1

I have a “ProductDescription” field as follows.

Table1

ProductDescripton	Qty	Colum2	Colum3	Colum4	Colum5
Pandacookielover
Pandacleaningmodel
Tigerneverlaugh
Tigerlaughtoomuch
Tomatorocks
Tomatoclubmonster
Onionflyhighhigh
Oninmissedpotato
Handbagcansing
Handbagoutdated
Toyknowsfashion
Cosmeticbeauty
Costmeticicecream
Toydreamsoftea
Lettuceguy
Orangejuicy
Shoeshatesocks
Shoesspeakchinese
Jacketdancer
Musechocolate
Lionking
Lionrabbit
Potatogame
Orangejacket
Onionmarrieslettuce
Shoestoetips
Cosmeticdancer
Musemomkitty
Toystory
Pandababy
Tigerrocket

Table2

ProductFamilyName	Code	ProductName
Zoo	Zoosection1	Tiger
Zoo	Zoosection2	Panda
Zoo	Zoosection4	Tigerfish
Zoo	Zoosection3	Muse
Zoo	Zoosection1	Lion
GroceryMkt	GroceryMktsection1	Tomato
GroceryMkt	GroceryMktsection1	Potato
GroceryMkt	GroceryMktsection2	Orange
GroceryMkt	GroceryMktsection1	Lettuce
GroceryMkt	GroceryMktsection1	Onion
DeptStore	DeptStoreSection1	Jacket
DeptStore	DeptStoreSection2	Shoes
DeptStore	DeptStoreSection2	Handbag
DeptStore	DeptStoreSection3	Toy
DeptStore	DeptStoreSection4	Cosmetic

Re: Is there anyway to count them in they way we want...?

Thu, 30 Oct 2014 13:38:43 GMT

Any input is appreciated please..:)

Re: Is there anyway to count them the way we want...?

Thu, 30 Oct 2014 14:28:23 GMT

Hi,

you could probably use this expression.

sum(if(wildmatch(ProductDescription,*-trim-*) or wildmatch(ProductDescription,*-box-*),0.5,1))

Re: Is there anyway to count them the way we want...?

Thu, 30 Oct 2014 14:45:48 GMT

Thank you, Shruthi! Do I use the expression as

sum(if(wildmatch(ProductDescription,*-trim-*) or wildmatch(ProductDescription,*-box-*),0.5,1)) * Qty in the Edit Expression window?

topic Re: Is there anyway to count them the way we want...? in QlikView

Is there anyway to count them the way we want...?

Re: Is there anyway to count them in they way we want...?

Re: Is there anyway to count them the way we want...?

Re: Is there anyway to count them the way we want...?