In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

REGEX

BRRLL99
11 - Bolide

INPUT :

 

field_1
Clearing Currency
Rate      Count      Table     Amount     Value    Value
 
 
ID      Credits                   Debits
 
Net       Merchandise Credit     35,0000
Net    Issuer Interchange   23,000DB
Net      Purchase Amount 10,000 GBP

 

 

Expected Output : it should add | whenever there is 2 spaces but only if contains Net | should be added before amount

i have tried this formula :

IF REGEX_Match([field_1], "^Net") THEN
REGEX_Replace(Trim([field_1]), "(\s{2,}|\t)+", "|") + "|"
ELSE
REGEX_Replace(Trim([field_1]), "(\s{2,}|\t)+", "|")
ENDIF

field_1
Clearing Currency|
Rate|Count|Table|Amount|Value|Value
 
 
ID|Credits|Debits
 
Net Merchandise Credit|35,000
Net Issuer Interchange|23,000DB
Net Purchase Amount|10,000 GBP
6 REPLIES 6
ChrisTX
16 - Nebula
16 - Nebula

This part is not easy to understand.  Can you re-word this:

Expected Output : it should add | whenever there is 2 spaces but only if contains Net | should be added before amount

 

Does the second table represent your expected output?

BRRLL99
11 - Bolide

Like you said

 

Yes 2nd table is my expected output

SPetrie
13 - Pulsar

Would this work?

if Contains([field_1],"Net") then
REGEX_Replace(REGEX_Replace([field_1],'\s\s+'," "),'(.+)(\s)(\d.+)',"$1|$3")
else
REGEX_Replace([field_1],'\s\s+',"|")
endif

LindonB
11 - Bolide

How about this....

IF

    ISNULL([field_1]) THEN NULL()
ELSE
    TRIM(
        IF CONTAINS([field_1], "Net") THEN
            REPLACE(
                REGEX_Replace(
                    REGEX_Replace([field_1], "(\s+\d)", "|$1"),
                "\s+",
                " "),
            "| ","|")
        ELSE
            REGEX_Replace([field_1], "\s{2,}", "|")
        ENDIF
    )
ENDIF

A bit more complicated, but it....

  1. When Null then keep Null.
  2. When "Net" is not present, replaces multiple spaces with "|".
  3. When "Net" is present...
    • Replaces multiple spaces with " ".
    • Adds "|" before the first number.
    • Removes the extra space that will be present before the "|"
  4. Then trims all remaining white space from the ends.

    Community1293902.PNG
binuacs
21 - Polaris

@BRRLL99 another method

image.png

 

flying008
15 - Aurora

Hi, @BRRLL99 

 

FYI.

 

Replace(REGEX_Replace(REGEX_Replace(Trim([field_1]), '\s(?=\d)', '  '), '\s{2,}', '|'), 'Net|', 'Net ')

 

 

field_1Get
Clearing CurrencyClearing Currency
Rate      Count      Table     Amount     Value    ValueRate|Count|Table|Amount|Value|Value
  
  
ID      Credits                   DebitsID|Credits|Debits
  
Net       Merchandise Credit     35,0000Net Merchandise Credit|35,0000
Net    Issuer Interchange   23,000DBNet Issuer Interchange|23,000DB
Net      Purchase Amount 10,000 GBPNet Purchase Amount|10,000 GBP
Labels
Top Solution Authors