DSXchange

A short text to describe your forum
http://dsxchange.com./

need help in logic

http://dsxchange.com./viewtopic.php?f=24&t=140445

Page 1 of 1

need help in logic

Posted: Mon May 30, 2011 7:45 am
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

Posted: Mon May 30, 2011 8:51 am
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:

Posted: Mon May 30, 2011 12:01 pm
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.

Posted: Mon May 30, 2011 5:03 pm
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.

Posted: Mon May 30, 2011 8:45 pm
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.

Posted: Mon May 30, 2011 11:02 pm
by ray.wurlod
Split the stream, perform the individual calculations, bring the streams back together (Link Collector) and perform the arithmetic.

Posted: Tue May 31, 2011 12:33 am
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

Posted: Tue May 31, 2011 11:55 am
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

Posted: Wed Jun 01, 2011 8:08 pm
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.

Posted: Sat Dec 17, 2011 9:57 am
by pandeesh
Thanks for your inputs!!

I have achieved this in transformer.
All times are UTC-06:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited