Need help with an exercise

Hello All,

I'm not sure how exactly to do this, given it's through the Alteryx Academy at a local community college. Am I allowed to post the file? I think that would be the best way to get help because you'll be able to see my workflow. Otherwise, I'm not sure that I'll be able to do a good job in explaining the issue.

Anyways, here's the question:

Reduce the data set to just the consignments with compensation. Convert the compensation to GBP for the consignments that are currently in USD and provide the following metrics:
a. the total number of compensation claims to be paid, and the total value of those claims
b. the total compensation from each carrier rounded to the nearest GBP
c. the top 3 months in terms of compensation claims to be paid, rounded to the nearest one hundredth, and with the € symbol

First of all, the rounding is killing me. I have no clue how to round to the appropriate 2-decimal digit number. Round and the other one Ceil, iirc, don't do this. They round to an integer. Next, I am completely clueless when it comes to c. I have no clue where to begin because the lesson didn't give us the tools or methods on how to do what it's asking - again with the rounding to the nearest hundredth and this appending of the Euro character.

I tried converting the data from fixed decimal to a string, using ToString(), but Alteryx didn't like it. And honestly, that's about the only tool in my bag of tricks for dealing with the question.

EDIT:

I'm going to include the question and what I've done. You can see my results along with the solution results - they're quite different. If this is a violation of the ToS, then I'll take it down. I feel that this isn't going to make much sense, unless you all can see what I'm working with.

a6q4.yxmd

Workflow

Tips and Tricks

Accepted answers

All comments

Deano478

@sameersheikh to get the values to 2 decimal places you can just use the select tool and set it fixed decimal type and 19.2 size and for the € symbol something like this should work

'&euro;' + ToString([Data])

gawa

@sameersheikh

Have you tried Round() function?

sameersheikh

I tried the

'&euro;' + ToString([Data])

and it didn't work.

I got the following error:

Formula (404) The formula "Compensation in GBP" resulted in a string but the field is numeric. Use ToNumber(...) if this is correct. (Expression #1)

sameersheikh

Interesting about Round(). It was not working. Everything I read showed that it rounded to an integer. I didn't know you could specify anything a decimal. I ran it, and it kind of worked. It does truncate the trailing zero, which I'd like to include.

CoG

That is incorrect (about the round tool). I was not used to the way the function worked when I first began using it, which led to frequent confusion and frustration on my part.

Round( [Number], x ) - The 'x' is not how many decimal places to round, but the multiple to round to. As @gawa - mentioned, Round( [Number], 0.01), would work out to 2 decimal places rounding (just make sure your data type is correct to handle decimal points)

The formula error means that you are outputting a string into a numeric column, which won't work. Either change the data type via drop-down or you will need to make a new field to store the output in.

sameersheikh

I've changed the data type from Fied Decimal to Int64; when I did that, I lost the decimals.

CoG

Exactly, do not use Int types if you want decimal values. The image I provided was for reference only, not advice on proper data types since you have a few different types it sounds like you are working with. The main point is to just make sure that you are using the correct ones.

sameersheikh

Anyone been able to look at the workflow that I've included? Others, when doing this exercise, are getting the identical numbers to the solutions, so I'm clearly not doing something right.

CoG

I looked at your workflow, and in the spirit of you doing your own work, I will provide you with a few pointers to help guide you, without giving you the answer:

Read the instructions very carefully and follow them step by step (your current workflow does not do this, and this is causing you most of your problems)
I also recommend deleting your work for the third question and re-working it, because there are many mistakes there and redundancies that will only confuse you further. Start that part of the flow again with a clean slate and see if you can figure it out.
Also for the third question don't forget that you have 2 date columns.

You are not far off. Best wishes! You can do it!!!

sameersheikh

How about a couple of hints mentioned at least what's wrong? It's likely that I'll repeat something very similar to the mess I created with my workflow, given I really don't know what's wrong with it other than maybe changing from fixed decimal to a double data type.

Deano478

Hey @sameersheikh I threw some hints into the WF for you along with fixing Q1 and Q2 hopefully this helps.

a6q4.yxmd

sameersheikh

Thank you so much for the hints. Unfortunately, I'm a bit lost. We haven't been taught the Union tool yet, so I don't really know what's going on there - why you'd use it, what it does, etc.

But allow me to take it piecemeal, starting with the first question. Why is my result off? I'm getting 6,132,812.55 whereas the solution is 6,185,908.71. My claims, like that of the solution are for 40 claims. Why is there a discrepancy in the sum figures?

Again, I'm pretty new to all of this, not just Alteryx but analytics as well; so, I don't have the experience or background to see something that all of you may find quite obvious.

EDIT:

I found the problem in 4a. There are existing claims that are already in GPB. I didn't see those, and I believe those were omitted in the summing.

Deano478

One reason is that you doing the conversion calculation on all records when the question says just do this for USD consignments only also at the very beginning you'll want to filter out any records that are 0 as the question again say you only want records with a compensation figure meaning > 0.

The union tool is essentially bringing all the data back together again after you've only done the calculation on the USD records the the formula after the union is just taking the value from the original currency column and putting it to fill the nulls there were the existing GBP records

sameersheikh

Okay, I think I got it. I mean, the solutions match, but I don't have a lot of confidence in what I did.

Thank you everyone for you help.

I'm enclosing my solution, which probably isn't as savvy as your solutions.

a6q4-solved.yxmd

Deano478

@sameersheikh it just takes some determinations is all and you got there in the end well done

sameersheikh

Couldn't have got to that point without the help of the community forcing me to churn those mental gears. So, a hearty thank you again

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280