Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
We need to trim field based on some conditions as below.
Please find the attached spreadsheet for more detail reference.
Supplier ID | Supplier ID | Expected Result |
0000112553_1300 | C0_112553 | 112553 |
0000112554_1200 | C1_112554 | 112554 |
0000112555_3456 | F1_112555 | 112555 |
0000112556_4568 | H0_112556 | 112556 |
0000112557_6544 | H1_112557 | 112557 |
0000112558_2456 | H2_112558 | 112558 |
Remove Zeros at starting and remove detail after '_' (example 112553) | Remove detail before '_' (example 112553) |
Thanks..
Try this
Table:
LOAD SubField(F1, '_', 1) * 1 as Expected1,
SubField(F2, '_', 2) as Expected2,
F1,
F2;
LOAD * INLINE [
F1, F2
0000112553_1300, C0_112553
0000112554_1200, C1_112554
0000112555_3456, F1_112555
0000112556_4568, H0_112556
0000112557_6544, H1_112557
0000112558_2456, H2_112558
];
What are the C,F,H ??
May be look SubField() function ?
Hi,
These were the prefix for the ID's.
The motive is to get both supplierId's from two different sources into one format.
Thanks..
Try this
Table:
LOAD SubField(F1, '_', 1) * 1 as Expected1,
SubField(F2, '_', 2) as Expected2,
F1,
F2;
LOAD * INLINE [
F1, F2
0000112553_1300, C0_112553
0000112554_1200, C1_112554
0000112555_3456, F1_112555
0000112556_4568, H0_112556
0000112557_6544, H1_112557
0000112558_2456, H2_112558
];
That means you have 2 fields already. So SubField() works for you?? Look in help
Hi
Maybe try 'Mid' function:
mid(FieldName,StartNum,EndNum) as NewValue
mid([Supplier ID], 5, 10) as New Value
I hope that helps.
Kind Regards
Paulina