Alteryx Designer Desktop Discussions

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

Regex

BRRLL99
11 - Bolide

i have following input , looking for expected output in only one formula tool

DATA
NIPO713|TRANSACTION MANAGEMENT SYSTEM|PAGE     1
RUN|PROD DATE  11/7/2024|SECOUND PRESENT|DATE   11/7/24
TIME 16:48:25
 
 
 
MEMBER ID  0009786|FILE TYPE 002
 
TRAN AMT|00000007568
 
PAN|12345678910|CURRENCY CONVERSION 11072024

 

Expected output : date, id and PAN should come in seperate column using formula tool, for amount column last 2 digits should be always in decimals ex : 7700   should be 77.00

dateidAmountcard
    
    
11/7/24   
    
    
  75.88 
 0009786  
   12345678910
4 REPLIES 4
flying008
15 - Aurora

Hi, @BRRLL99 

 

[DATE] = 

IIF(Contains([DATA], 'DATE'), REGEX_Replace([DATA], '^.*\|DATE[ \s]+', ''), Null())

 

[ID] = 

IIF(Contains([DATA], 'ID'), REGEX_Replace([DATA], '^.*?ID[ \s]+(\d+)\|.*$', '$1'), Null())

 

[AMOUNT] = 

IIF(Contains([DATA], 'AMT|'), ToNumber(REGEX_Replace([DATA], '^\D+(\d+)$', '$1')) / 100 , Null())

 

[CARD] =  

IIF(Contains([DATA], 'PAN|'), REGEX_Replace([DATA], '^\w+\|(\d+)\|.*$', '$1'), Null())

 

录制_2024_11_07_14_50_24_824.gif

BRRLL99
11 - Bolide

@flying008 

for Amount column im getting 0

 

my data will be 

TRAN AMT|00000007568|MSG REV IND

TRAN AMT|00000009968|MSG REV IND

TRAN AMT|00000007000|MSG REV IND

 

EXPECTED OUTPUT :
75.68

99.68

70.00

flying008
15 - Aurora
IIF(Contains([DATA], 'AMT|'), ToNumber(REGEX_Replace([DATA], '^\D+(\d+)\|?.*$', '$1')) / 100 , Null())
BRRLL99
11 - Bolide

can i get output like this based on ID 

if ID is not null then amount and card should come opposite to ID

 

dateidAmountcard
    
    
11/7/24   
    
    
    
 000978675.8812345678910
    
Labels
Top Solution Authors