Routine Fix Repeat

[New2DS]

Hello All,

I am repating this post
viewtopic.php?t=92268&highlight=routine+fix
as I tried my best to fix but no luck. So guys I need some help here.

Below is my routine and it works like a charm.(The database count field is '0' upto this point and I have to populate the values based on this routine results)

Code: Select all

If (CurrMCol <> PrevMCol) AND (CurrPCol <> PrvPCol) then
   If StCode = '20' OR StCode = '29' then Count = 0
   Else Count = 1
End

If (CurrMCol = PrevMCol) AND (CurrPCol = PrvPCol) Then 
      If (EffCurrDate = MaxPrevDate or EffCurrDate = MaxPrevDt + 1) then 
          If (StCode = '20' OR StCode = '29' ) then 
             If ( EntryDate >= StartDate and EntryDate <= EndDate) then Count = 1 
             Else If (MaxEndDt = EndDate) then Count = 0 
             Else If ( IsNull(EntryDate) or EntryDate = '') then Count = 0  
          End
      End
End

If I get any of the conditions below I am getting a blank count. So I need some help to add these conditions in the else part if CurrMCol = PrevMcol and CurrPCol = PrvPCol in the same loop.

Code: Select all

If (CurrMCol = PrevMcol and CurrPCol = PrvPCol) and
  
     If (EffCurrDate <> MaxPrevDate or EffCurrDate <> MaxPrevDt + 1) then 
         IF Not(IsNull(EntryDate) then count =1 else count = 0

        Else if (EffCurrDate = MaxPrevDate or EffCurrDate = MaxPrevDt + 1) then
                if (StCode <> '20' OR StCode <> '29' ) then 
                   If Not(IsNull(EntryDate) then Count = 1 
        Else count = 0
End

Hope I get some help in adding the above condition in my routine.

thanks

[ray.wurlod]

A routine must return its result through a variable called Ans- your routine code does not appear to assign a value to this variable.

[New2DS]

A routine must return its result through a variable called Ans- your routine code does not appear to assign a value to this variable.

Ray, Sorry I forgot to copy the Ans. The routine should return the count. Here it is

Code: Select all

Ans = Count

Here is an example and CNBR( 3 rd column is the key)

MNBR PNBR CNBR DSC_DT MIN_EFF_DT MAX_END_DT DS_CNT StCode Days

MB222222222 903534 11111111112 9999-12-31 2004-02-23 2004-02-29 0 30 7
MB222222222 903534 11111111113 9999-12-31 2004-03-01 2004-03-31 0 30 31
MB222222222 903534 11111111114 2004-04-30 2004-04-01 2004-04-30 0 30 30
MB222222222 903534 11111111115 9999-12-31 2004-05-01 2004-05-31 0 30 31
MB222222222 903534 11111111116 9999-12-31 2004-06-01 2004-06-02 0 30 1

MB555555555 901535 222222222222 9999-12-31 2004-01-31 2004-01-31 0 30 1
MB555555555 901535 222222222223 2004-02-14 2004-02-01 2004-02-14 0 06 13
MB555555555 901535 222222222224 9999-12-31 2004-02-25 2004-02-29 0 30 5
MB555555555 901535 222222222225 2004-03-10 2004-03-01 2004-03-10 0 06 9
MB555566666 901555 2222222111125 2004-03-10 2004-03-01 2004-03-10 0 30 15
MB555566666 901555 2222222111126 2004-03-10 2004-03-01 2004-03-10 0 06 12

Below is the logic I have to implement

Code: Select all

If the Current.MNBR = Previous.MBNR and Cuurrent.PNBR = Previous.PNBR and
      If  (current.min_eff_dt = Previous.max_end_Dt or current.min_eff_Dt = Previous.Max_end_dt+1) and 
          If  Stcode = 30 or 99 then do the following

If the  DSC_DT between 2004-01-01 and 2004-12-31 then ds_cnt =1
If the DSC_DT = 2004-12-31 then ds_cnt = 0
If dsc_dt = 9999-12-31 (Null Date) then dsc_cnt = 0

If the current and previous columns are equal and If stcode is not in (30,99) and dsc_dt is not null date 9999-12-31 then 1 

If the current and previous columns not equal and dsc_dt is not null date 9999-12-31 then 1

else 0

Result: The rows 3,7,9,10 and 11 should a DSC_cnt =1 rest 0 and I also have to add the corresponding days.

Ex: the row 3 should get a dsc_cnt of 1 and days=68 (7+31+30) as the three records have the same MBNR and PNBR.

Here the whole point is if a patient stayed for 3 months he should get Dsc_Cnt =0 and days=0 for first 2 months and in the last month he should get a dsc_cnt =1 and sum of days of three months.

Hope I will be helped with some ideas to implement this kind of logic.

thanks

[ray.wurlod]

You originally specified Routine, but now you're using link-qualified column names in the expression.

Are you using a column derivation expression in a Transformer stage, or a Routine? If you're using a Routine, are you providing the values in a single argument, or multiple arguments?

If you're using a derivation expression, you only get one If..Then..Else. That is, your derivation must be of the form

Code: Select all

If (expression) Then (result1) Else (result2)

Of course, each of the three expressions (expression, result1 and result2) can be nested expressions, including If..Then..Else expressions.

Since the result is required only to be 1 or 0, you can bypass one level of If..Then..Else and rely on the 1=true, 0=false convention in DataStage BASIC.

I don't have access to DataStage this week, but you should be able to build an expression with the above. If not, post again.

[New2DS]

Ray,

Thanks for the quick reply. I am taking all the column as arguments in the routine and trying to implement the logic. Ex: the InLink.MBNR will be stored in a stagevariable as sv1 and my routine arguments will be the InLink.MBNR, sv1 to compare the current and previous MBNR column values.

But some home I am not getting the expected results becuase I think I am missing some of the else statements. Can you please post the sample derivation so that I will implement that tomorrow morning.

thanks

[ray.wurlod]

I don't have access to DataStage this week. Will follow up (or someone else will) soon.

[New2DS]

I don't have access to DataStage this week. Will follow up (or someone else will) soon.

Thanks Ray. I added the count of days logic also in my previous post.

thanks