Newbie: I'd like to double check if each of the sales transactions are accurately priced

[tsao88]

dataset 1: a list of sales transactions with various parameters including shipping methods, gift wrapped, free samples taken, etc. (the conditions is usually marked with 'X'

Sales record ID	Shipping methods	Gift wrapped	Free samples requested
1	x
2	x	x
3	x	x	x

dataset 2: official price list :if three of the above conditions are met, it's $10 for the services;
if two of the above conditions are met, it's $8;
if one of the above condition is met, it's $5

Shipping methods	Gift wrapped	Free samples requested	Package	Fee
x	x	x	A	$10
x	x		B	$8
x			C	$5

Could you please advise on which tools I might use to have Dataset 1 to scan through Dataset 2, scanning from left to right and top to bottom? I would appreciate any guidance you can provide?

Desired result

Sales record ID	Package	Fee
1	C	$5
2	B	$8
3	A	$10

[TUSHAR050392]

happy to help ! please find my response -

1. You can use the function LEN(Match Key) in a formula tool which will give you the length of match key if it is only made of X. For e.g. XXX will have length of 3.

2. If you have a numeric field, you should convert into a string by either using a select tool or within the formula when you are generating match key, you can use Tostring function for that column to make it string. Your match key formula will look like - Column1 + Column 2 + Tostring(Column3) + Column4.

If anyone of your field is numeric, it will give you a syntax error because you are trying to concatenate string with numbers.

Hope this is helpful and can be marked as a solution

[tsao88]

(1) If I have more than 10 additional parameters to consider in addition to 'Shipping, Gift Wrapped, Free Samples, Coupon Code, Air mail'. Is there a more efficient way to link the field columns, or I can only do it manually by [Shipping]+[Gift Wrapped]+[Free Samples]+[Coupon Code]+[Air mail]+etc.?

(2) When comparing the sales records to my price list, the coupon code could be 12345 or 54321. What's the proper way to establish a formula that would take either one of 12345 or 54321 as input to the 'Match Key' column? In addition, what formula tool could I use to filter-in the last digit in the a coupon code and disregard the first 4 digits? 

(3) Any suggestions of formula tool for the application of purchase discount when a proper coupon code was applied, as well as a maximum shipping fees ($10 for continental USA) 

Thanks!

[TUSHAR050392]

Hey,

1) I think that should the easiest way or else you can use transpose plus cross tab but that will make it complicated.

2) When you are creating the match key, make sure that you use the same order of columns or else it will lead to mismatch. To get the last digit, just use right(coupon code,1) to get the last digit.

3) I am not sure what the question is here but if you are asking that you want to do some calculations with if there is a coupon code then you can probably use if else statement. For example - IF coupon code = 12345 THEN price*0.9 (10% discount) ELSEIF coupon code = 54321 THEN price*0.8 (20% discount) ELSE price END.

Hope this helps

[tsao88]

My match key formula (Column1 + Column 2 + Tostring(Column3) + Column4 + etc) returns 'null' in the new match key column, however I was expecting to see a series of letters and numbers such as Match Key 'XYZ321'..

[TUSHAR050392]

@tsao88 Can you provide a screenshot of the formula and data so that I can look? Only reason it will return null if it has everything blank because for data type issues, formula tool will give a syntax error.

[tsao88]

The formula is straight forward and all in pink/red colors, no signs of syntax errors, however the result returns NULL. 

Although the match key was a bit longer, about 30+ characters consisting of letters and numbers, it didn't match data at all.

[Field1] + [Field2] + (Field3] + [Field4] + [Field5] .... Field 40]

I've also tried TOSTRING([Field1] + [Field2] + (Field3] + [Field4] + [Field5] .... Field 40]), it returns NULL as well

[TUSHAR050392]

@tsao88 For matching, you need to ensure that your fields are in the same order on both sides otherwise, they won't match. For the null values, please use a select tool before the formula tool and check if all fields are string data type. Can you also share a screenshot of select tool showing all your fields if possible?