Forum Discussion

KevinMorneault's avatar
KevinMorneault
Icon for Helper II rankHelper II
4 years ago
Solved

How to copy a specific string from a column to another

Hi All I would like to know if this can be done.  I have a column which has text (Summary). I would like to know if i can add a column(ExpenseCode) and copy only some part of the needed from(Summary...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Ah, interesting.  Yes, that does change things a bit.  I am going to assume you want the three values in three different columns: Expense Code, GL Code, Invoice Number.  
    We add three calculated columns, each set up to look for a different string and pull back all the text after that string up to the next carrige return (UNICHAR(10)) like so:

    Expense Code = 
VAR _Code = "Expense Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
    GL Code = 
VAR _Code = "GL Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
    Invoice Number = 
VAR _Code = "Invoice Number : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

    My sample data has 3 rows that look like this.  You can see that the first entry only had the Expense Code so that is the only one that comes back.

    Joh Doe
    IT Equipment Request
    Expense Code : 1234
    Device: Laptop
    Model: Latitude
    Cost: 1K
    this system needs to be install with windows 11 and other application
    please verify with the user ...... etc..
    Joh Doe
    IT Equipment Request
    Expense Code : 6654654654654
    GL Code : 987654
    Invoice Number : 789321654798
    Device: Laptop
    Model: Latitude
    Cost: 1K
    this system needs to be install with windows 11 and other application
    please verify with the user ...... etc..
    Joh Doe
    IT Equipment Request
    Expense Code : 1455654
    GL Code : 987654
    Invoice Number :
    Device: Laptop
    Model: Latitude
    Cost: 1K
    this system needs to be install with windows 11 and other application
    please verify with the user ...... etc..

     

     

  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    It looks like you need to adjust the _Code VAR to match your real data.

    Expense Code = 
VAR _Code = "Expense Code : "

    This first VAR is set to the string "Expense Code : ",  this is the string it is searching for in the data but in your data it looks like the string is "ExpenseCode: " so you would have to change the measure to match.

    Expense Code = 
VAR _Code = "ExpenseCode: "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
4 years ago

Ah, interesting.  Yes, that does change things a bit.  I am going to assume you want the three values in three different columns: Expense Code, GL Code, Invoice Number.  
We add three calculated columns, each set up to look for a different string and pull back all the text after that string up to the next carrige return (UNICHAR(10)) like so:

Expense Code = 
VAR _Code = "Expense Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
GL Code = 
VAR _Code = "GL Code : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )
Invoice Number = 
VAR _Code = "Invoice Number : "
VAR _CodeLen = LEN ( _Code )
VAR _CodeMissing = ISERROR ( FIND ( _Code, 'DataTable'[Column] ) )
VAR _CodePosition = IF ( _CodeMissing, BLANK (), FIND ( _Code, 'DataTable'[Column] ) + _CodeLen )
VAR _CodeEnd = IF ( _CodeMissing, BLANK (), FIND ( UNICHAR ( 10 ), 'DataTable'[Column], _CodePosition ) )
RETURN
    IF ( _CodeMissing, BLANK (), MID ( 'DataTable'[Column], _CodePosition, _CodeEnd - _CodePosition ) )

My sample data has 3 rows that look like this.  You can see that the first entry only had the Expense Code so that is the only one that comes back.

Joh Doe
IT Equipment Request
Expense Code : 1234
Device: Laptop
Model: Latitude
Cost: 1K
this system needs to be install with windows 11 and other application
please verify with the user ...... etc..
Joh Doe
IT Equipment Request
Expense Code : 6654654654654
GL Code : 987654
Invoice Number : 789321654798
Device: Laptop
Model: Latitude
Cost: 1K
this system needs to be install with windows 11 and other application
please verify with the user ...... etc..
Joh Doe
IT Equipment Request
Expense Code : 1455654
GL Code : 987654
Invoice Number :
Device: Laptop
Model: Latitude
Cost: 1K
this system needs to be install with windows 11 and other application
please verify with the user ...... etc..

 

 

KevinMorneault's avatar
KevinMorneault
Icon for Helper II rankHelper II
4 years ago

Hi 

Tks it is reallly appreciated. I will give it a try and update. Basically this is within Remedy via the DWP module. When a request for NewIT Equipment is send, they need to fill out a form which contains section like the following.

Approval Information

Request Information

Reques for information

Computer information

Monitor information

Software information

Peripheral information

Printer Information

Business Requirement/Rational

 

All of this have approx a total of 60 line all dump in one cell for each WO created.  At first we had requested for the to create a table to make it easier for us to handle but thats not what they did, its a dump in the ticket. Now What i am trying to do is basically extract the information needed to validate against our asset management. 

 

IF what you provided me work, i will adjust and export/add more column for the device information and other information. I try to think how can this be done differently but so far its the only way i can think of.

 

I will try what you provided and update you.

 

Tks again