Trying to complete Practice Exercise 3 from the learning path

[mxledesma]

Hello, 

I used the Join tool to join the initial state and the order details.  I noticed that there is an order ID associated with each customer ID. So I joined them. The results came out to 15 customer IDS. The answer is 10 order IDS with each customer. I am NOT sure what to do from there.   Also, I am getting a parse error when I try to find the # of shipping date = order date - Shipping date. Also i do know that the discount is the  Total saved which I do not know how to add the column. This is my work which needs some corrections. 

If I cannot get the proper order #s. I cannot do the rest.  The data sets are attached. 

[DavidP]

You only need 1 join tool to combine the 2 data sets. When joining, you have to find the common element between the 2 data sets and tell the join tool to use this field or fields to join the 2 data sets. You do this by picking the field from the left and right data sets underneath the "Join by specific fields" option.

When you look at Ship date after the 1st Select Tool, you can see that the values are all null, so changing the data type to Date did not work. This is because Ship date is not in the correct format of yyyy-mm-dd. Use a DateTime parse tool instead to convert Ship date to the right format. It will create a field with a new name, so you can then use your Select tool to uncheck the original ship date field and rename then new field to Ship date.

To get the difference in days between Order date and Ship Date, try using the DateTimeDiff() function

For Total Cost you have to multiply unit cost by quantity, add shipping cost and subtract discount. I wouldn't branch off to do this, just do it straight after your days to ship calculation.

[mxledesma]

David, Thanks for your reply. Let me upload what i have so far. I did what you told me but i am getting a parse error. On the datetime diff(), i am not sure what I use for "u". Once when i have the # of shipping days i can calculate the rest. Also, the other thing that concerns me is in the Join Function, i think the only element to join is the Customer ID correct?

Thanks, 

Michael ledesma

[mxledesma]

I forgot to upload the fiie i have so far. It is under my Practice Exercise 3 my work.

[DavidP]

u in datetimediff is the unit you want to display the result in, such as "day", "week", "month", "year", so the formula becomes

DateTimeDiff([Order Date],[Ship Date 2],'day')

You're correct with the join function.

[mxledesma]

David, 

I am getting a parse error on the second formula tool. To me it looks good. Not sure where the expression is wrong. 

To me, the total saved is the discount. So not sure what it is asking. 

I am uploading what i have so far. 

[DavidP]

Hi @mxledesma, you're getting close to the solution. Let's look at the problem with the formula first.

Here's how I troubleshoot a problem like this:

1. Look at the error - it says Invalid type in subtraction operator. You can also see in the formula on the left that [Discount] is a different color. 

2. My suspicion is that [Discount] is the wrong data type, so next I make sure I click on the input anchor of the formula tool in question to look at the data coming in. Below in the results preview window, I also toggle to Metadata. 

3. I can see that [Discount] is a String (text) datatype whereas the rest are all Double (numeric)

4. So the error is telling me that I can't subtract a text value from a number.

To fix this, you can change the data type of [Discount] in the Select tool (2 tools previous) 

One thing I noticed is that where you calculate Days to Ship, you set the Data type to String - this needs to be chanced to Double, or another number type.

You're right that Saving is the discount. The last part of the exercise is to join the Order details with the Customer information. This reduces the data to only 15 rows. But these rows contain line item transactions for 10 orders, so you need to group the transactions by order.

This is done with a Summarise tool. With this tool you can group the transactions by Order ID and then you can do a number of things within each group:

1. You can add up The total cost that you calculated for each line.

2. You can find the minimum value of Days to Ship

3. You can add up the total Discount to give you Total Saving

[mxledesma]

David,

Thanks for the explanation. I am almost there. At my output of my summarize tool i have 14 rows. But the answer as you mentioned has 10 rows. I do noticed that I have several duplicate order IDs.  But, i do not know how to pull those and add each discount to get the total savings or the Total cost of each order. Thanks for not giving me the design answer abut explaining it to me in a sentence so i can figure it out as a designer. If you can explain how to pull those duplicate orders IDs. to give me the correct 10. 

Signed confused. 

Michael Ledesma

[mxledesma]

David, 

Sorry i keep on forgetting to put my work. 

Michael ledesma

[DavidP]

Hi @mxledesma,

You're very welcome. I believe that the learning value of building the solution yourself is 100x more than if someone just gave you the solution. 

You're super close now to the final outcome. 

When using tools like Summarize, I like to test at every step - that way you can see when thing go off the rails! 

As the name suggests, using the group by action creates a group and everything you then do is done within the context of that group. 

You correctly grouped by Order ID - this in itself will give you the 10 unique order IDs, but since you also group by Days to Ship, the group is created based on both criteria, hence you get more than 10 records.

You don't have to remove Days to ship, just click on the action button next to it so that you can change the action. We're looking for the Minimum Days to Ship for each order, so you can change the action to Min to achieve this.

One more thing though - Days to Ship is still defined as a Text variable. To correctly get the minimum value, it needs to be a number type. You can fix this by going back to the formula tool where Days to Ship is defined and just change the data type to Double or Int16.

You won't see an error, but Text values sorted from min to max will look like this:  1,11,2,22,3...  whereas number values will be 1,2,3...