4 Replies Latest reply: Jun 11, 2012 10:24 AM by Rob Wunderlich RSS

    Load: Add a constant as a value in Field1 based on value of Field 2

    Joe Kirwan

      I have a Trans table:

       

      Month

      InvNo

      Item

      Ctgy

      Val

      Jan

      1

      A

      XY

      10

      Jan

      2

      B

      W

      20

      Jan

      3

      A

      XY

      15

      Jan

      4

      C

      W

      12

      Jan

      5

      A

      Z

      20

      Feb

      6

      A

      XY

      15

      Feb

      7

      A

      Z

      17

      Feb

      8

      B

      W

      18

      Feb

      9

      C

      W

      11

      Feb

      10

      A

      XY

      14

       

       

      I have a Rate table:

       

      Month

      Rate

      Jan

      0.7

      Feb

      0.8

       

      I use this code to load the rate field into the Trans table,

       

      LEFT Join (Trans)
      LOAD Pd,
           'A'
      as Item,
           'XY'
      as Ctgy,
          
      Rate
           Resident 

      EDIT: This should read Resident Rate

      Month

      InvNo

      Item

      Ctgy

      Val

      Rate

      Jan

      1

      A

      XY

      10

      0.7

      Jan

      2

      B

      W

      20

      -

      Jan

      3

      A

      XY

      15

      0.7

      Jan

      4

      C

      W

      12

      -

      Jan

      5

      A

      Z

      20

      -

      Feb

      6

      A

      XY

      15

      0.8

      Feb

      7

      A

      Z

      17

      -

      Feb

      8

      B

      W

      18

      -

      Feb

      9

      C

      W

      11

      -

      Feb

      10

      A

      XY

      14

      0.8

      This gives me:

       

      NOW, I WANT to Load the Constant 1 as the Rate for any Item A, which has a Ctgy of ‘Z’.

       

      Ie I want this

       

      Month

      InvNo

      Item

      Ctgy

      Val

      Rate

      Jan

      1

      A

      XY

      10

      0.7

      Jan

      2

      B

      W

      20

      -

      Jan

      3

      A

      XY

      15

      0.7

      Jan

      4

      C

      W

      12

      -

      Jan

      5

      A

      Z

      20

      1.0

      Feb

      6

      A

      XY

      15

      0.8

      Feb

      7

      A

      Z

      17

      1.0

      Feb

      8

      B

      W

      18

      -

      Feb

      9

      C

      W

      11

      -

      Feb

      10

      A

      XY

      14

      0.8

       

       

      I thought   this might work:

       

      LEFT Join (Trans)
      LOAD
           'A'
      as Item,
           'Z'
      as Ctgy,
          
      Num(1) as Rate
           Resident Trans

       

      But the values stay at ‘-‘

       

      Any ideas ?