Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table where I'd like to create a set of derived columns based on columns but I'm having a lot of difficulty with coding the logic. Any help would be highly appreciated!
Suppose my current table is such that:
Current table:
Product Type | Fruit | Color | Color Known? | Individual Color | Count |
Individual | Apple | N | 4 | ||
Individual | Grapes | Y | Red | 6 | |
Basket | Apple | Red | 1 | ||
Basket | Apple | Yellow | 3 | ||
Basket | Apple | Green | 2 | ||
Basket | Grapes | Green | 2 | ||
Basket | Grapes | Red | 4 | ||
Basket | Orange | Orange | 3 |
Essentially, suppose fruit is being sold two ways: individually vs. combined in a basket. We want to be able to calculate the number of fruit associated with each color, with drill-down into type. If fruit is being sold individually, we either know or do not know the color. If known, we can designate the color in the "Individual Color" column. If unknown (i.e. Color Known? = N), then we can assume the color distribution based on the distribution of same fruit type in a basket and then allocate to each color.
For example, we do not know the color of individual apples but know that a basket of apples being sold contains 1 Red, 3 Yellow, and 2 Green (i.e. total 6 apples). In other words, in the basket, 17% is Red (1/6), 50% is Yellow (3/6), and 33% (2/6) is Green. Applied to the individual apples (total 4), we can assume there are 0.7 Red apples (4 * 0.17), 2 Yellow apples (4 * 0.5), and 1.3 Green apples (4 * 0.33). Assume decimals are allowed.
As a result our final table should look like the below:
Final data table
Product Type | Fruit | Color | Final Color | Count | Final Count |
Individual | Apple | Red | 0.7 | ||
Individual | Apple | Yellow | 2 | ||
Individual | Apple | Green | 1.3 | ||
Individual | Grapes | Green | 6 | ||
Basket | Apple | Red | Red | 1 | 1 |
Basket | Apple | Yellow | Yellow | 3 | 3 |
Basket | Apple | Green | Green | 2 | 2 |
Basket | Grapes | Green | Green | 2 | 2 |
Basket | Grapes | Red | Red | 4 | 4 |
Basket | Orange | Orange | Orange | 3 | 3 |
In doing so, I can then build a chart with dimensions Final Color and Fruit to get the total # of fruit for each color.
I'm trying to implement these during the Load script. I am trying to get the Final Color logic such that:
Likewise, Final Count column logic should be:
For Final Count, I tried creating an intermediary column in the load such that:
Load * ,
sum(Total <[Product Type], [Fruit], [Color]> [Count]) as [Calculation1]
However I ran into an error saying that Sum only allows 1 parameter even though the sum formula works fine as an expression when creating a table.
Thanks in advance!
Hi, are you sure the error is because of that row of code? it only shows one parameter for sum.
You can try with rangesum if you need to add more than one field.
Or upload a sample so we can take a look.
Here is the Help link for Sum as used in the Script, which is potentially different than what is allowed in an Expression, so be sure to compare things:
I agree with Ruben otherwise, you likely will have to attach a sample app etc. in order for folks to be able to help further, as this is one of those where you likely need to see a lot more in order to try to figure out what is going wrong.
Regards,
Brett