Help with string function reading data from files

Question

Question

Ahmad Bukhtiar · Nov 19, 2020

#Code Snippet #Coding Guidelines #Unstructured Data #Caché #Ensemble

I have multiple files with different columns, first 9 values are fixed, so i want to ignore the first value, and next 8 values i want to combine into one value using ^ sign

Current Format

|||||||||||^^||||||^^|||||||||||||||||
|||||||||||^^||||^^|||||||||||||||||||||||
|||||||||||^^|||^^||||||||

Desired Format

^^^^^^|||^^||||||^^|||||||||||||||||
^^^^^^|||^^||||^^|||||||||||||||||||||||
^^^^^^|||^^|||^^||||||||

Reading each line from the file use below code.

#dim line as %String = tInput.ReadLine(, .status)

"here i was to put some string function to change format of the data in line variable"

set status = ..SetEncounter(.line, tFileType, tLineNumber)

Discussion (11)2

Log in or sign up to continue

Vitaly Furman · Nov 20, 2020

Hi Ahmad,

Your added code seems correct. Except for the where you add the remainder to the newline... $PIECE(line, "|", 8) gets the content of the 8th column. remainder begins with the content of the 9th column, without any separator between them. So maybe $PIECE(line, "|", 8)_"^"_remainder or $PIECE(line, "|", 8)_"|"_remainder if you want to keep the original separator between 8th and 9th columns.

Also I assume you have Set tLineNumber = 0 before the while loop. Then, set tLineNumber = tLineNumber + 1 makes sense at the beginning of the loop (I usually initialize at 1 and would have the increment at the very end of the loop).

Also, SetPatientContact takes "line" by reference... I assume there is a reason for that... usually strings in COS can be passed by value... unless you are going to change it inside SetPatientContact; then it makes sense.

0 0

Vitaly Furman · Nov 21, 2020

Hi,

I may try this later if I have time by I think I know why this would not work.

SourceTable.Field2 is a static Property so I don't think you can change its name on the fly.

I have to run now but I would suspect that we may be able to get to the Property name with a Dynamic SQL Query and maybe set it while iterating through the ResultSet

0 0

Robert Cemper · Nov 22, 2020

For i=2:1:$LENGTH(line) { 
    set $PROPERTY(SourceTable, "Field"_i) = $PIECE(line, "|", i)using $PROPERTY()
   }

Use $PROPERTY()

2 0

score 0 · Answer 1 · 2020-11-19T23:00:34-05:00

Hi Ahmad,

Maybe something like this?

Method SetEncounter(line, ByRef newLine, ByRef remainder, pFileType, pLineNumber) As %Status {

Set tSC = $$$OK

Set tNewField = ""

For i=2:1:$LENGTH(line) {

Set word = $PIECE(line, "|", i)

If i=2 {Set newField = word}

Else {Set newField = newField_"^"_word}

If i>8 Quit // If you don;t care what happens with columns 9 and after

}

Set remainder = $PIECE(line, "|", 9, $LENGTH(line))

Set newLine = tNewField_"^"_remainder

Quit tSC

// Here we go through the line by examining each field of your column, starting with column 2. So we just build your output with each iteration. You probably have to save your original line and and grab the remainder from it to concatenate with new line

score 0 · Answer 2 · 2020-11-20T08:19:07-05:00

Thanks, your code gave me the idea, Do you think below code will fix the issue and ".line" value will be changed? I will do testing but it will take time so review new code below pls and advice if anything wrong is there.

while ('tInput.AtEnd) {
       #dim line as %String = tInput.ReadLine(, .status)
       quit:$$$ISERR(status)
       set tLineNumber = tLineNumber + 1
       //20112020: new code added
       Set remainder = $PIECE(line, "|", 9, $LENGTH(line))
       Set newline = $PIECE(line, "|", 2)_"^"_$PIECE(line, "|", 3)_"^"_$PIECE(line, "|", 4)_"^"_$PIECE(line, "|", 5)_"^"_$PIECE(line, "|", 6)_"^"_$PIECE(line, "|", 7)_"^"_$PIECE(line, "|", 8)_remainder
       Set line = newline
       //20112020: new code end
       if tLineNumber > 1 {
        If (tFileType="PatientContact")   {
        set status = ..SetPatientContact(.line, tFileType, tLineNumber)

score 0 · Answer 3 · 2020-11-20T16:16:27-05:00

Hi Vitaly, this is existing code written by earlier team, linenumber+1 is there, the code first create globals and then from globals push the data to ECR. One question for reconciliation purpose I can create one table and push data to that table to i can store the flat files data into a table for example "SourceData" so I can do some validation on data prior to pushing to ECR like mandatory fields are not empty, code fields have allowed codes etc.

I can do that

Set table.field1 = $PIECE(line, "|", 1)

set status = table.%save()

OR

I can use your For logic above to go over the each value in the line but setting to table field can I use "i" variable like

For i=2:1:$LENGTH(line) {

set SourceTable.Field_i = $PIECE(line, "|", i)

score 0 · Answer 4 · 2020-11-20T16:22:43-05:00

Vitaly Furman · Nov 20, 2020

Hi Ahmad, I believe this should work

0 0

score 0 · Answer 5 · 2020-11-20T20:56:59-05:00

Thanks will try, is there a way to run queries on globals like get distinct values from allergy code across all patients?

also now data is stored in SDA from flat file can I query SAD.Allergy to get distinct allergy codes and validate how many patients have allergy record but with no allergy code value provided in flat files?

score 0 · Answer 6 · 2020-11-21T12:47:26-05:00

Globals are not my strongest suite... I am not sure how the data elements are stored in Globals. That said, I think I can answer your 2nd question. HealthShare will not store Allergies without AllergyCode. The SDA is actually only an intermediate format for the HealthShare Data Model; the data is stored in the Streamlet database on each Edge Gateway. You will get an Alert in your Ensemble Production; something like "An Allergy Code needed to store Allergy".

To answer your more general question, such as "How many patients are there with a specific Allergy Code?", I have to admit that HealthShare is quite poor for running report-style aggregated queries of type "select count(Patient), AllergyCode from ... group by AllergyCode". The Streamlets are stored as XML Objects with no discrete data fields and have to be loaded into memory using, for example, an API method LoadSDAObject(pStreamletId, ByRef pSDA) in the HS.SDA3.Streamlet.ContainerMethods class. pSDA would be your HS.SDA3.Allergy object. But these objects are of type %SerialObject, not %Persistent. Data for all patients on an Edge Gateway are typically stored in a single global ^HS.SDA3.Streamlet.AbstractD (you can use the System Explorer -> Globals browser to see how it looks).

The only time I remember we needed to aggregate Patient/Encounter data we had to write custom code to pull it out of the global. And even after that, we had to transfer the data to a SQL server to run aggregated queries.

I think HealthInsight is a product where these aggregated queries are handled better.

I hope this helps.

score 0 · Answer 7 · 2020-11-21T18:38:59-05:00

This didn’t work I would need to write 60 lines for each column Field9, Field10 I can generate the code lines using excel but it’s just like doesn’t look good. Any idea you can give that will help

For i=2:1:$LENGTH(line) {

set SourceTable.Field_i = $PIECE(line, "|", i)

score 0 · Answer 8 · 2020-11-21T21:38:48-05:00

Ahmad Bukhtiar · Nov 21, 2020

yes correct i tried dynamic sql and it worked

0 0