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 |
