need help in logic

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

need help in logic

Post by pandeesh »

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
pandeeswaran
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :evil:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
jyothisdasms
Participant
Posts: 33
Joined: Wed May 19, 2010 12:15 am
Location: Pune

Post by jyothisdasms »

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.
" Dream like you will live forever, live like you will die today."
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

There's no Remove Duplicates stage in Server jobs, but the Aggregator stage can be set up to yield First or Last record in group.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

But in case of 1 having three values, how to find the difference using FIRST and LAST.

in case of two values we can utilize FIRST-LAST for difference.
pandeeswaran
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Split the stream, perform the individual calculations, bring the streams back together (Link Collector) and perform the arithmetic.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
peddakkagari
Participant
Posts: 26
Joined: Thu Aug 12, 2010 12:07 am

Post by peddakkagari »

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
Top
arunkumarmm
Participant
Posts: 246
Joined: Mon Jun 30, 2008 3:22 am
Location: New York
Contact:
Contact arunkumarmm

Post by arunkumarmm »

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:

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
Top
rschirm
Premium Member
Premium Member
Posts: 27
Joined: Fri Dec 13, 2002 2:53 pm

Post by rschirm »

How about using the agregator, group on ID, sum on the Value, Min on the Value Max on the value.

Then on the output to transformerstage calculate the diff.
Top
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Thanks for your inputs!!

I have achieved this in transformer.
pandeeswaran
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”