December 27, 2011 at 2:14 pm
hello,
I need to write something in SSIS that will say if my percentages does not equal 100 % then add the total, and whatever the difference is +/- i should use the biggest percentage and add the difference to that percentage to equal the whole to 100.
example.
all fields are in string/text in db.
IDPERCENTTYPEPERCENT
ABCDEFGH091011.42
ABCDEFGH093511.54
ABCDEFGH092013.36
ABCDEFGH094513.74
ABCDEFGH092513.78
ABCDEFGH094017.46
ABCDEFGH09552.85
ABCDEFGH09503.5
ABCDEFGH09155.02
ABCDEFGH09307.3
since the total of percent is < 100 (99.97), i need to add this difference (0.03) to type = 40.
and, vice versa, if the total of percent is > 100, i need to subtract the difference from the biggest type to make it equal 100.
can someone help?
December 27, 2011 at 2:19 pm
ankita.patel01 81294 (12/27/2011)
hello,I need to write something in SSIS that will say if my percentages does not equal 100 % then add the total, and whatever the difference is +/- i should use the biggest percentage and add the difference to that percentage to equal the whole to 100.
example.
all fields are in string/text in db.
IDPERCENTTYPEPERCENT
ABCDEFGH091011.42
ABCDEFGH093511.54
ABCDEFGH092013.36
ABCDEFGH094513.74
ABCDEFGH092513.78
ABCDEFGH094017.46
ABCDEFGH09552.85
ABCDEFGH09503.5
ABCDEFGH09155.02
ABCDEFGH09307.3
since the total of percent is < 100 (99.97), i need to add this difference (0.03) to type = 40.
and, vice versa, if the total of percent is > 100, i need to subtract the difference from the biggest type to make it equal 100.
can someone help?
This looks like a straightforward T-SQL problem - can you explain why you want to use SSIS please?
Also, can you explain why the PERCENT column is not saved in a numeric column?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 27, 2011 at 2:23 pm
that is how we're receiving the data as text. and, need to use ssis to convert this data to proper data type and transform with these scenarios to load to another ods table every month.
December 27, 2011 at 2:27 pm
ankita.patel01 81294 (12/27/2011)
that is how we're receiving the data as text. and, need to use ssis to convert this data to proper data type and transform with these scenarios to load to another ods table every month.
But you said
all fields are in string/text in db.
But I think that you're saying that the data arrives in a CSV file and that you are loading it into a database - is that correct?
Please try to be clear with your requirements - or you'll end up receiving incorrect advice.
What does 'ods' mean?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 27, 2011 at 2:32 pm
yes, it is coming in as a csv file and being loaded to a table that has all varchar data types. i need to join this table with another and create a snapshot table with this transformation in it.
December 29, 2011 at 5:54 am
Hi,
Please find the steps to handle the issue:-
1. First you should a Dataconversion to get it in a proper format.
2. Use the Aggregate Task to do the SUM for the Percent Column.
3. Use the "Conditional Split Task" to check the condition if it >100 or < 100.
4. After this use the Derived column in each split to get the difference.
5. You can add or subtract that difference based on the requirement.
I hopw this should handle your requirement. Please let me knoiw in case of any clarification
Regards,
Chandrashekhar
December 29, 2011 at 7:43 am
thanks chandrashekar. i have done that. i'm stuck on the derived column part. i have a conditional split to split into those records that are 100%, some that are errored and some that will need adjusting. I am doing max of the percent column and adding a derived variance column (100 - sum(percent)). I'm not sure how to tie the reccords to use this derived variance column in addition to all other percent columns coming in. can you advise?
December 29, 2011 at 11:13 am
I figured this out.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply