In this post, I would like to explain what we should do in trouble using CrossTab tool.
CrossTab tool is very useful tool. But when you want to calculate using transposed fields by CrossTab tool, do you in trouble because there is no transposed fields.
I would like to explain about the specific example. For example, please imagine that you work at a retail store. A retailer wants to know the percentage of laundry detergent and kitchen detergent sold. In this case, you can use the formulas as follows.
[laundry_detergent]/([laundry_detergent]+[kitchen_detergent])*100Normally, you will make such as this workflow which the sales data is to pivot and caluculate the ratio.
As a result, you can get this result.
However when you confirm the progress at the beginning of the month, sometimes there is a lack of data. For example, if there is no sales on laundry detergent, CrossTab tool can’t generate “laundry_detergent” field, so the workflow will show the error.
Now let’s look at the Formula tool. Indeed, “kitchen_detergent” field is becoming black and not recognized as a field.
So I would like to explain two solutions.
My Solution
Solution 1. Add header only
The 1st solution is adding headers only before the calculation to hold the needed fields. The specific way is that you make header data and union the pivoted data by using the Union tool. However if the fields don’t have any records, they will have Null data, so you have to replace them with 0 by using the Formula tool. The workflow is as follows.
You can make the header using the Text Input tool. You only set the header names as headers as follows.
After that, you have to union the data and this header. And if there are no records, the calculate results will be Null, so they should be set them to 0 at the Formula tool. When there are many fields which you have to set to 0, you can use the Multi-Field Formula tool to be efficient.
Solution 2. add the 0 before pivot
Firstly, this way is very easy way but we should be do carefully.
This way is that adding the fields which have 0 values before using the CrossTab tool. Note that if you add the 0 value, they don’t affect summing up, but if you count the records, you will get the wrong result. However this is easy way bacause you add the 0 data and nothing else is needed. The workflow is as follows.
The Text tools setting is as follows. That is the data that you set fields name and 0 value. But as I mentioned earlier, the values are not affected for summing up but affected for counting records.
The setting of the Formula tool is no needed to change.
Sampleworkflow Download
*This workflow uses “Data Science 100 Knocks (Structured Data Processing Edition)” by the Skill Definition Committee of the Data Scientists Association of Japan
Contents of the next blog
The next post will be tips for the Join tool.
コメント