need help in logic
Moderators: chulett, rschirm, roy
need help in logic
HI,
I need to develop a server job for the below logic:
My input file is a sequential file which contains 2 columns
id,mark
---------
l1,20
l1,49
l2,45
l3,23
l3,56
l3,23
My output file should contain 3 columns and the result should like this:
id,sum,diff
------------
l1,69,29
l2,45,45
l3,102,0
diff should be always +ve value.
In aggregator stage we can group based on id value.
Sum can be caluculated ny utilizing the SUM function.
How to calculate the diff value?
Any help and suggestion welcome.
Thanks
I need to develop a server job for the below logic:
My input file is a sequential file which contains 2 columns
id,mark
---------
l1,20
l1,49
l2,45
l3,23
l3,56
l3,23
My output file should contain 3 columns and the result should like this:
id,sum,diff
------------
l1,69,29
l2,45,45
l3,102,0
diff should be always +ve value.
In aggregator stage we can group based on id value.
Sum can be caluculated ny utilizing the SUM function.
How to calculate the diff value?
Any help and suggestion welcome.
Thanks
pandeeswaran
Start first by fully explaining your requirements. For example, this "difference" column - how is it calculated? What exactly is it the difference of? Don't expect people to just magically know or expect them to puzzle it out for you.
And please stop using "+ve", there's no such dang word.
And please stop using "+ve", there's no such dang word.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 33
- Joined: Wed May 19, 2010 12:15 am
- Location: Pune
Use a transformer stage with some stage variables like
Stagevarnew
Stagevarprev
Map the Stagevarnew to the Stagevarprev.Assign the key value(Here first column) to the Stagevarnew.Compare both stage variables for the group by Process.add/find out the difference for the rows having same key values.
Minus(-) can be avoided by doing a check of less than zero and multiplying it with '-1'
Use a remove duplicate stage and keep the last value after the transformation.
Hope this is what you want.
Stagevarnew
Stagevarprev
Map the Stagevarnew to the Stagevarprev.Assign the key value(Here first column) to the Stagevarnew.Compare both stage variables for the group by Process.add/find out the difference for the rows having same key values.
Minus(-) can be avoided by doing a check of less than zero and multiplying it with '-1'
Use a remove duplicate stage and keep the last value after the transformation.
Hope this is what you want.
" Dream like you will live forever, live like you will die today."
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 26
- Joined: Thu Aug 12, 2010 12:07 am
Connect your source to Aggregator and put group by on ID and do SUM and MAX and connect these three columns(ID,sum_value,max_value) to trasformer.
In Trasformer calculate DIFF using the logic
ABS(max_value*2 - sum_value) , here ABS function used to get the positive value
and connect three fields(id,sum,diff) to target
Thanks,
Sreekanth
In Trasformer calculate DIFF using the logic
ABS(max_value*2 - sum_value) , here ABS function used to get the positive value
and connect three fields(id,sum,diff) to target
Thanks,
Sreekanth
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
I believe there is a typo in your output. From what I understand, your output should be:
id,sum,diff
------------
l1,69,29
l2,45,45
l3,102,10
If my understanding is correct, I created a small job and tested it. Try and let us know:
Assuming your source is a flat file:
id,sum,diff
------------
l1,69,29
l2,45,45
l3,102,10
If my understanding is correct, I created a small job and tested it. Try and let us know:
Assuming your source is a flat file:
Code: Select all
BEGIN HEADER
CharacterSet "CP1252"
ExportingTool "IBM Websphere DataStage Export"
ToolVersion "8"
ServerName "L52DSAD2"
ToolInstanceID "Annuity"
MDISVersion "1.0"
Date "2011-05-31"
Time "13.53.52"
ServerVersion "8.1"
END HEADER
BEGIN DSJOB
Identifier "Test_SumDiff"
DateModified "2011-05-31"
TimeModified "13.49.40"
BEGIN DSRECORD
Identifier "ROOT"
OLEType "CJobDefn"
Readonly "0"
Name "Test_SumDiff"
NextID "1"
Container "V0"
JobVersion "50.0.0"
ControlAfterSubr "0"
MetaBag "CMetaProperty"
BEGIN DSSUBRECORD
Owner "APT"
Name "AdvancedRuntimeOptions"
Value "#DSProjectARTOptions#"
END DSSUBRECORD
NULLIndicatorPosition "0"
IsTemplate "0"
JobType "0"
Category "\\Jobs\\New_Development\\Arun"
CenturyBreakYear "30"
NextAliasID "2"
ParameterFileDDName "DD00001"
ReservedWordCheck "1"
TransactionSize "0"
ValidationStatus "0"
Uploadable "0"
PgmCustomizationFlag "0"
JobReportFlag "0"
AllowMultipleInvocations "0"
Act2ActOverideDefaults "0"
Act2ActEnableRowBuffer "0"
Act2ActUseIPC "0"
Act2ActBufferSize "0"
Act2ActIPCTimeout "0"
ExpressionSemanticCheckFlag "0"
TraceOption "0"
EnableCacheSharing "0"
RuntimeColumnPropagation "0"
RelStagesInJobStatus "-1"
WebServiceEnabled "0"
MFProcessMetaData "0"
MFProcessMetaDataXMLFileExchangeMethod "0"
IMSProgType "0"
CopyLibPrefix "ARDT"
RecordPerformanceResults "0"
END DSRECORD
BEGIN DSRECORD
Identifier "V0"
OLEType "CContainerView"
Readonly "0"
Name "Job"
NextID "1"
IsTopLevel "0"
StageList "V0S0|V0S3|V0S4|V0S8"
StageXPos "168|672|432|888"
StageYPos "312|312|312|312"
StageTypes "CSeqFileStage|CTransformerStage|CCustomStage|CHashedFileStage"
NextStageID "13"
SnapToGrid "1"
GridLines "0"
ZoomValue "100"
StageXSize "48|48|48|48"
StageYSize "48|48|48|48"
ContainerViewSizing "0108 0140 1168 0706 0000 0001 0040 0000"
StageNames "Sequential_File_0|Transformer_3|Sort_4|Hashed_File_8"
StageTypeIDs "CSeqFileStage|CTransformerStage|sort|CHashedFileStage"
LinkNames "DSLink2|DSLink7|DSLink5| "
LinkHasMetaDatas "True|True|True| "
LinkTypes "1|1|1| "
LinkNamePositionXs "323|807|508| "
LinkNamePositionYs "336|336|336| "
TargetStageIDs "V0S4|V0S8|V0S3| "
SourceStageEffectiveExecutionModes "0|0|0| "
SourceStageRuntimeExecutionModes "0|0|0| "
TargetStageEffectiveExecutionModes "0|0|0| "
TargetStageRuntimeExecutionModes "0|0|0| "
LinkIsSingleOperatorLookup "False|False|False| "
LinkIsSortSequential "False|False|False| "
LinkSortMode "0|0|0| "
LinkPartColMode "0|0|0| "
LinkSourcePinIDs "V0S0P1|V0S3P2|V0S4P2| "
END DSRECORD
BEGIN DSRECORD
Identifier "V0S0"
OLEType "CSeqFileStage"
Readonly "0"
Name "Sequential_File_0"
NextID "2"
OutputPins "V0S0P1"
UnixFormat "1"
PipeStage "0"
UnicodeBOM "1"
UnicodeSwapped "1"
AllowColumnMapping "0"
WithFilter "0"
StageType "CSeqFileStage"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S0P1"
OLEType "CSeqOutput"
Readonly "0"
Name "DSLink2"
Partner "V0S4|V0S4P1"
FileName "/data/annuity/temp/TestDiff.txt"
ColDelim ","
QuoteChar "000"
ColHeaders "0"
FixedWidth "0"
ColSpace "0"
EnforceMetaData "0"
Readtimeout "0"
Columns "COutputColumn"
BEGIN DSSUBRECORD
Name "Field01"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "1"
DisplaySize "0"
Group "0"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "Field02"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
Group "0"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
Waitforwriter "0"
PadChar ""
LeftTextPos "323"
TopTextPos "336"
SuppressTruncWarn "0"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S3"
OLEType "CTransformerStage"
Readonly "0"
Name "Transformer_3"
NextID "4"
InputPins "V0S3P1"
OutputPins "V0S3P2"
ValidationStatus "0"
StageType "CTransformerStage"
BlockSize "0"
StageVars "CStageVar"
BEGIN DSSUBRECORD
Name "svOld"
Expression "svNew"
InitialValue "''"
SqlType "3"
ParsedExpression "svNew"
StageVars "svNew"
Precision "0"
ColScale "0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "svNew"
Expression "DSLink5.Field01"
InitialValue "''"
SqlType "3"
ParsedExpression "DSLink5.Field01"
SourceColumn "DSLink5.Field01"
Precision "0"
ColScale "0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "svNewRec"
Expression "If svOld <> svNew Then 'Y' Else 'N'"
InitialValue "''"
SqlType "3"
ParsedExpression " If svOld <> svNew Then 'Y' Else 'N'"
StageVars "svOld;svNew"
Precision "18"
ColScale "4"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "svSum"
Expression "If svNewRec = 'Y' Then Trim(DSLink5.Field02) Else svSum+Trim(DSLink5.Field02)"
InitialValue "0"
SqlType "3"
ParsedExpression " If svNewRec = 'Y' Then Trim(DSLink5.Field02) Else svSum + Trim(DSLink5.Field02)"
SourceColumn "DSLink5.Field02"
StageVars "svNewRec;svSum"
Precision "0"
ColScale "0"
ExtendedPrecision "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "svDiff"
Expression "If svNewRec = 'Y' Then Trim(DSLink5.Field02) Else OConv(svDiff,\"MCN\")-Trim(DSLink5.Field02)"
InitialValue "0"
SqlType "3"
ParsedExpression " If svNewRec = 'Y' Then Trim(DSLink5.Field02) Else OConv(svDiff, \"MCN\") - Trim(DSLink5.Field02)"
SourceColumn "DSLink5.Field02"
StageVars "svNewRec;svDiff"
Precision "0"
ColScale "0"
ExtendedPrecision "0"
END DSSUBRECORD
END DSRECORD
BEGIN DSRECORD
Identifier "V0S3P1"
OLEType "CTrxInput"
Readonly "0"
Name "DSLink5"
Partner "V0S4|V0S4P2"
LinkType "1"
MultiRow "0"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S3P2"
OLEType "CTrxOutput"
Readonly "0"
Name "DSLink7"
Partner "V0S8|V0S8P1"
Reject "0"
ErrorPin "0"
RowLimit "0"
Columns "COutputColumn"
BEGIN DSSUBRECORD
Name "ID"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "1"
DisplaySize "0"
Derivation "DSLink5.Field01"
Group "0"
ParsedDerivation "DSLink5.Field01"
SourceColumn "DSLink5.Field01"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "Sum"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
Derivation "svSum"
Group "0"
ParsedDerivation "svSum"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
StageVars "svSum"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "Diff"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
Derivation "OConv(svDiff, \"MCN\")"
Group "0"
ParsedDerivation "OConv(svDiff, \"MCN\")"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
StageVars "svDiff"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
LeftTextPos "807"
TopTextPos "336"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S4"
OLEType "CCustomStage"
Readonly "0"
Name "Sort_4"
NextID "3"
InputPins "V0S4P1"
OutputPins "V0S4P2"
StageType "sort"
AllowColumnMapping "0"
Properties "CCustomProperty"
BEGIN DSSUBRECORD
Name "SORTSPEC"
Value "Field01 A"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "MAXROWSINVM"
Value "10000"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "TEMPDIR"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "ESCCHAR"
Value "\\"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "TRACLVL"
Value "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "STABLESORT"
Value "no"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "COLSEP"
Value ","
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "MAXOPENFILES"
Value "10"
END DSSUBRECORD
NextRecordID "0"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S4P1"
OLEType "CCustomInput"
Readonly "0"
Name "DSLink2"
Partner "V0S0|V0S0P1"
LinkType "1"
ConditionNotMet "fail"
LookupFail "fail"
TransactionSize "0"
TXNBehaviour "0"
EnableTxGroup "0"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S4P2"
OLEType "CCustomOutput"
Readonly "0"
Name "DSLink5"
Partner "V0S3|V0S3P1"
Columns "COutputColumn"
BEGIN DSSUBRECORD
Name "Field01"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "1"
DisplaySize "0"
Group "0"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "Field02"
SqlType "12"
Precision "10"
Scale "0"
Nullable "0"
KeyPosition "0"
DisplaySize "0"
Group "0"
SortKey "0"
SortType "0"
AllowCRLF "0"
LevelNo "0"
Occurs "0"
PadNulls "0"
SignOption "0"
SortingOrder "0"
ArrayHandling "0"
SyncIndicator "0"
PadChar ""
ExtendedPrecision "0"
TaggedSubrec "0"
OccursVarying "0"
PKeyIsCaseless "0"
SCDPurpose "0"
END DSSUBRECORD
LeftTextPos "508"
TopTextPos "336"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S8"
OLEType "CHashedFileStage"
Readonly "0"
Name "Hashed_File_8"
NextID "2"
InputPins "V0S8P1"
Directory "/data/annuity/hash"
UVcompatible "0"
SQLNull "0"
StageType "CHashedFileStage"
END DSRECORD
BEGIN DSRECORD
Identifier "V0S8P1"
OLEType "CHashedInput"
Readonly "0"
Name "DSLink7"
Partner "V0S3|V0S3P2"
FileName "H_TestDiff"
ClearFile "0"
Backup "0"
DisableCW "0"
CreateFileOptions "DYNAMIC MINIMUM.MODULUS 1 GROUP.SIZE 1 GENERAL SPLIT.LOAD 80 MERGE.LOAD 50 LARGE.RECORD 1628"
DeleteFile "1"
END DSRECORD
END DSJOB
Arun