cancel
Showing results for 
Search instead for 
Did you mean: 

Multiplication Error

Former Member
0 Kudos

Hi,

I have to multiply two columns and give an output, but i am not getting the decimal places as required.

I am multiplying Col 1 (decimal 13,4) with Col 2(decimal 9,2) to get an output Col3 (decimal 13,2).

For example - when i multiply 161.25 * 1 i am getting 161; when i multiply 78.6234 * 1 i am getting 78.6.

How can i resolve this?

Thanks,

Arun

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

About 161.25 * 1. It should give you 161.25 itself since the product column scale is 2 - Col3 (decimal 13,2).

About 78.6234 * 1, you cannot expect 78.6234 as product. For this, make your product column scale to 4 - (decimal 13,4) atleast.

I would recommend to have a check on product column of target table, all the preceding transformations and the source table / file format to have a scale of 4 atleast. You should give the maximum scale to the product column to make sure that the decimals are not shrinked.

Suppose you maintain Col 1 (decimal 13,4) * Col 2(decimal 9,2) to get an output Col3 (decimal 13,4).

It should look like this:

161.2500 * 1.00 = 161.2500

78.6234 * 1.00 = 78.6234

Regards,

Suneer.

Former Member
0 Kudos

But i am getting 161 instead of 161.25 and 78.6 instead of 78.62. If the output column is decimal (13,2) then it should be 161.25 instead of 161. right?

Does it counts the decimal dot as one character? I was wondering whether DI automatically truncates the decimal?

Arun

Former Member
0 Kudos

What are the source data types? How many transforms are used in between? What is your target database?

Former Member
0 Kudos

My source data type is decimal. Three query transforms, one validation and one pivot.

Target database is sql server 2008 and tables are generated from template table in DI.

Former Member
0 Kudos

Check for Col 1 (decimal 13,4) with Col 2(decimal 9,2) in source.

Now if you are multiplying in any of the transformations mentioned,

Check for the product column to be Col 3 (decimal 13,4).

Check that these scales are followed in all the transforms. If you are Pivoting the columns, pivot data should also have the minimum scale of 4 .ie. decimal (13,4). Check each one by one.

Also, double click the target table and see what scale is maintained on the left side. If you maintain the target as a template table, it will always recreate on the schema of the preceding transform. Better to keep it imported with the above mentioned scale.

Regards,

Suneer

Former Member
0 Kudos

Hi Suneer,

As suggested, i changed all the transformations and schema to 13,4 instead of 13,2.

Now the final output is coming with 4 zeros in the decimal places.

Like 161.25 * 1 gives 161.0000 instead of 161.2500. Also 78.6 became 78.6000 in the output.

Why the value not taking digits and only zeros in the decimals?

Also i am using the multiplication inside a ifthenelse condition. For example

ifthenelse(Col X>0, (Col 1 * Col2), Null). Do i have to add any function like round or trunc before the multiplication?

Arun

Former Member
0 Kudos

Check for the Col1 to be 13.2 (atleast) in all transformations till you do the multiplication.

I hope you see 161.25 in the source and you are getting 161.25 * 1 = 161.0000

That means .25 is getting truncated somewhere through the path.

What is the datatype of Product column where you use ifthenselse?

Your mapping rule is correct. You do not have to do anything extra in the mapping rule. You are pretty close.

Regards,

Suneer

Former Member
0 Kudos

Hi Suneer,

I checked in and out and all the data types are decimal. I could not find any thing. When i execute in debug mode, i do not see any problem until the output table. Transformation before the output table is showing correctly, but when it reaches the output the figure display changes.

Actually the output is Qty X Unit Cost and the final output is in Currency or Cost.

I did not see an option of currency in DI, so do you advise any other data type instead of currency? Is decimal data type OK instead of currency?

Arun

Former Member
0 Kudos

Now that you are getting all decimal till target, it is the target table datatype which is rounding off. In DI, there is no Currency / Money datatype. It will be interpreted as Decimal. Go with decimal datatype in the target database table as well with the very same precision and scale.

Regards,

Suneer

Former Member
0 Kudos

Hey Suneer,

I finally figured out the issue. The issue is occurring in the last query.

When i multiply the columns, the output should not be zero. I am multiplying Col1 * Col2 and if output is zero then i am replacing it with 0.01.

If i remove this condition from the query then output comes correctly.

But i need this condition. Is there any other way of doing this.

Thanks,

Arun

Former Member
0 Kudos

I finally solved the issue. I used a Validation transform to insert value instead of zero in the output.

Now it works well.

Appreciate your help.

Arun