SOLVED
Regex
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
BRRLL99
11 - Bolide
‎11-06-2024
09:52 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
date | id | Amount | card |
11/7/24 | |||
75.88 | |||
0009786 | |||
12345678910 |
Solved! Go to Solution.
Labels:
- Labels:
- Regex
4 REPLIES 4
flying008
15 - Aurora
‎11-06-2024
10:50 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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())
‎11-06-2024
11:12 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
‎11-06-2024
11:53 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
IIF(Contains([DATA], 'AMT|'), ToNumber(REGEX_Replace([DATA], '^\D+(\d+)\|?.*$', '$1')) / 100 , Null())
‎11-07-2024
12:32 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
can i get output like this based on ID
if ID is not null then amount and card should come opposite to ID
date | id | Amount | card |
11/7/24 | |||
0009786 | 75.88 | 12345678910 | |
![](/skins/images/D34B41DA407DC996E7BFF253AD24F7E2/responsive_peak/images/icon_anonymous_message.png)