[X]My Assistant

[Dinodeserter]

I have a report that I want to extract data from a column of information. This is the IV Guage, date it was inserted, and if it was inserted in or out of the hospital. The information can change and may not always be the same. I have read on some other forums that people have used the POS and another function to find the position of the characters to be parsed. I was wondering if an IF or IIF statement would work to see if the information is in the string and then send it to another column(which is what I would like to happen. Here is a sample excel file. Thanks!!
 Peripheral_IV_Report_HIN_Revised___4758046_1_.zip ( 11.66K )Number of downloads: 6

[R. Hicks]

I have not downloaded your file .. but appears you need to use the InStr() function to do what you need.
You should be able to find the correct syntax and usage examples by searching for this function.
.

[dmhzx]

Instr() possibly in conjunction with Mid(). and maybe Replace
nstr() will tell you if and where one string contains another
You can then use the postion found by Instr(), with MID() to grab that bit of string and move it.
But of course, you don't need to do that if you already know the string you went, so as long as INSTR returns gretaer than 0, that may be all you need.
If you wnt to remove it from the original string, Replace is very good.

[Dinodeserter]

Here's a screen shot of the functions listed. I don't see Instr() listed under them but does that mean I can still use it? Also do you have a good example of the syntax? Maybe how to combine the functions which is what really stumps me. (Sorry this BI is really new to me!)
 PowerInsightScreenShot.jpg ( 396.87K )Number of downloads: 7

[dmhzx]

Profuce apologies: I didn't read the title propelry.
I was so much into Access, that I didn't notice that it was Business Objects you were working with.
So sorry to have got your hopes up.

[R. Hicks]

I am sorry as well ... I just assumed you were experiencing your immediate problem in Access (VBA).
.

[jwhite]

How to use the Pos Function

[Dinodeserter]

Thanks jwhite! That is perfect! It shows exactly how to make the string and piece it together. I understand now how the syntax is created by using the variable builder (well, maybe not completely understand).
Here's two of the pictures which show some of the process just in case anyone is looking on here for the answer to this.
 15pos.png ( 19.24K )Number of downloads: 2
 17street_20address.png ( 18.25K )Number of downloads: 1

[Dinodeserter]

I worked on this and with another source helping me we came up with this code for the variables:

CODE

Repl1Var=Replace(Replace(Replace(Replace(Replace([Label Name];"Left";"ABC");"Right";"ABC");"Lower";"ABC");"Upper";"ABC");"ABC, ABC";"ABC")
ateVar=Substr([Repl1Var];Pos([Repl1Var];"ABC")+4;17)
GaugeVar=Substr([Repl1Var];1;Pos([Repl1Var];"gauge")+4)
HospVar=Substr([Repl1Var];Pos([Repl1Var];[DateVar])+17;1000)

Using the Repl1Var helps to divide up the row and then take out the pieces that I want. The only problem is that sometimes the guage of the IV isn't documented and I have to give up the location of where it was placed, hence the replace for lower, upper, left, and right.
I was thinking that something with the SQL that used something like Expr1=such and such would allow me to make my own variables on the query side which could then be placed into the report. The only problem is that this SQL is confusing for me to understand. Here is the code and the piece I think I need to affect is the CE_DYNAMIC_LABEL which is what creates the label name on the powerinsight side (at least I think so!):

CODE

SELECT
  ENCNTR_ALIAS.ALIAS,
  to_date(to_char(( pi_from_gmt(ENCOUNTER.REG_DT_TM,( pi_time_zone(1) )) ), 'mm/dd/yyyy'), 'mm/dd/yyyy'),
  [b]CE_DYNAMIC_LABEL.label_name
[/b]
,
  CV_NURSE_UNIT.DISPLAY,
  CV_ROOM.DISPLAY,
  PERSON.NAME_FULL_FORMATTED,
  V500_EVENT_SET_CODE.EVENT_SET_NAME,
  pi_from_gmt(CE_DYNAMIC_LABEL.updt_dt_tm,( pi_time_zone(1) ))
FROM
  ENCNTR_ALIAS,
  CODE_VALUE  CV_ENCNTR_ALIAS_TYPE,
  ENCOUNTER,
  [b]CE_DYNAMIC_LABEL[/b],
  CODE_VALUE  CV_NURSE_UNIT,
  CODE_VALUE  CV_ROOM,
  PERSON,
  V500_EVENT_SET_CODE,
  CUST_V500.CUST_ORGANIZATION,
  CODE_VALUE  CV_ENCOUTER_TYPE,
  CLINICAL_EVENT,
  CODE_VALUE  CV_RESULT_STATUS,
  V500_EVENT_SET_EXPLODE
WHERE
  ( ENCOUNTER.ENCNTR_TYPE_CD=CV_ENCOUTER_TYPE.CODE_VALUE AND ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( ENCOUNTER.PERSON_ID=PERSON.PERSON_ID AND ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID
AND ENCNTR_ALIAS.ACTIVE_IND = 1
AND ENCNTR_ALIAS.END_EFFECTIVE_DT_TM > SYSDATE AND ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( ENCNTR_ALIAS.ENCNTR_ALIAS_TYPE_CD=CV_ENCNTR_ALIAS_TYPE.CODE_VALUE  )
  AND  ( ENCOUNTER.LOC_NURSE_UNIT_CD=CV_NURSE_UNIT.CODE_VALUE AND ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( CV_ROOM.CODE_VALUE=ENCOUNTER.LOC_ROOM_CD AND ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( ENCOUNTER.ENCNTR_ID=CLINICAL_EVENT.ENCNTR_ID AND ENCOUNTER.ACTIVE_IND = 1 AND ENCOUNTER.PERSON_ID = CLINICAL_EVENT.PERSON_ID  )
  AND  ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID  )
  AND  ( CV_ENCNTR_ALIAS_TYPE.CODE_VALUE=ENCNTR_ALIAS.ENCNTR_ALIAS_TYPE_CD  )
  AND  ( ENCOUNTER.PERSON_ID=PERSON.PERSON_ID  )
  AND  ( CLINICAL_EVENT.RESULT_STATUS_CD=CV_RESULT_STATUS.CODE_VALUE  )
  AND  ( V500_EVENT_SET_EXPLODE.EVENT_SET_CD=V500_EVENT_SET_CODE.EVENT_SET_CD  )
  AND  ( V500_EVENT_SET_EXPLODE.EVENT_CD=CLINICAL_EVENT.EVENT_CD  )
  AND  ( PERSON.ACTIVE_IND = 1  )
  AND  ( ENCNTR_ALIAS.ENCNTR_ID=ENCOUNTER.ENCNTR_ID
AND ENCNTR_ALIAS.ACTIVE_IND = 1
AND ENCNTR_ALIAS.END_EFFECTIVE_DT_TM>PI_TO_GMT(sysdate, PI_TIME_ZONE(1))
AND ENCOUNTER.ACTIVE_IND = 1
  )
  AND  ( ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( CLINICAL_EVENT.CE_DYNAMIC_LABEL_ID = CE_DYNAMIC_LABEL.CE_DYNAMIC_LABEL_ID  )
  AND  ( CUST_V500.CUST_ORGANIZATION.ORGANIZATION_ID=ENCOUNTER.ORGANIZATION_ID AND ENCOUNTER.ACTIVE_IND = 1  )
  AND  ( PERSON.NAME_LAST_KEY NOT IN ('PROD','PRODE','PRODTEST','PRODETEST','PRODCTEST','INTERFACE','LEAPFROG')  )
  AND  ( ENCOUNTER.NAME_LAST_KEY NOT IN ('PROD','PRODE','PRODTEST','PRODETEST','PRODCTEST','INTERFACE','LEAPFROG')  )
  AND  ( CV_ENCNTR_ALIAS_TYPE.CODE_SET = 319
and CV_ENCNTR_ALIAS_TYPE.CDF_MEANING = 'FIN NBR'
and ENCNTR_ALIAS.end_effective_dt_tm>PI_TO_GMT(sysdate, PI_TIME_ZONE(1))
and ENCNTR_ALIAS.active_ind=1  )
  AND  
  (
   CUST_V500.CUST_ORGANIZATION.ORG_NAME_KEY  =  'ADVENTISTHINSDALEHOSPITAL'
   AND
   (
    CV_NURSE_UNIT.CODE_VALUE  IN  ( 656262786  )
    AND
    CV_ENCOUTER_TYPE.DISPLAY  IN  ( 'Inpatient','Observation','Outpatient-B'  )
   )
   AND
   ENCOUNTER.DISCH_DT_TM  Is Null  
   AND
   (
    (
     CLINICAL_EVENT.EVENT_CD  IN  ( 378099834, 378100944, 378099931, 378100009, 378100055, 378100145, 378100187, 378100903  )
     AND
     (
      CV_RESULT_STATUS.DISPLAY  IN  ( 'Auth (Verified)','Modified'  )
      AND
      CLINICAL_EVENT.VALID_UNTIL_DT_TM  >=  SYSDATE
     )
     AND
     CLINICAL_EVENT.EVENT_END_DT_TM
  BETWEEN  ( pi_to_gmt(TRUNC(SYSDATE),( pi_time_zone(2) )) ) - 15  AND  SYSDATE
    )
    AND
    V500_EVENT_SET_CODE.EVENT_SET_NAME  =  'Peripheral Line RG'
    AND
    (
     pi_get_cv_display(CE_DYNAMIC_LABEL.LABEL_STATUS_CD)  =  'Active'
     AND
     CE_DYNAMIC_LABEL.create_dt_tm
  BETWEEN  ( pi_to_gmt(TRUNC(SYSDATE),( pi_time_zone(2) )) ) - 15  AND  SYSDATE
    )
    AND
    CE_DYNAMIC_LABEL.valid_until_dt_tm
  >  SYSDATE
   )
  )