UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Find And Parse Data In A String, BI XI    
 
   
Dinodeserter
post Dec 12 2013, 09:39 AM
Post#1



Posts: 332
Joined: 7-December 12



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!!
Attached File  Peripheral_IV_Report_HIN_Revised___4758046_1_.zip ( 11.66K )Number of downloads: 4
Go to the top of the page
 
R. Hicks
post Dec 12 2013, 09:43 AM
Post#2


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


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.
.
Go to the top of the page
 
dmhzx
post Dec 12 2013, 10:26 AM
Post#3



Posts: 7,033
Joined: 22-December 10
From: England


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.
Go to the top of the page
 
Dinodeserter
post Dec 12 2013, 10:56 AM
Post#4



Posts: 332
Joined: 7-December 12



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!)
Attached File  PowerInsightScreenShot.jpg ( 396.87K )Number of downloads: 7
Go to the top of the page
 
dmhzx
post Dec 12 2013, 11:00 AM
Post#5



Posts: 7,033
Joined: 22-December 10
From: England


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.
dunce.gif
Go to the top of the page
 
R. Hicks
post Dec 12 2013, 11:05 AM
Post#6


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


I am sorry as well ... I just assumed you were experiencing your immediate problem in Access (VBA).
.
Go to the top of the page
 
jwhite
post Dec 12 2013, 11:09 AM
Post#7


UtterAccess VIP
Posts: 5,804
Joined: 31-August 06
From: North Carolina, USA


How to use the Pos Function
Go to the top of the page
 
Dinodeserter
post Dec 12 2013, 12:32 PM
Post#8



Posts: 332
Joined: 7-December 12



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.
Attached File  15pos.png ( 19.24K )Number of downloads: 2
Attached File  17street_20address.png ( 18.25K )Number of downloads: 1
Go to the top of the page
 
Dinodeserter
post Dec 14 2013, 01:35 PM
Post#9



Posts: 332
Joined: 7-December 12



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
   )
  )
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 07:57 AM