Pull 8 Digit value out of Comment field

[crystles]

I have a data set that has a Comments section and in this section they usually put Missing items in it.I want to be able to pull those Item numbers out of the Comments field.

Comment Field Examples.

• Missing: 12345678
• ItemName 12345678 Item description
• Item description 12345678

So it is very random where the numbers may end up in the code. I do have a list of all items I could match it too to pull out the number, but I can't figure out how that would work.

Any help or ideas would be greatly appreciated. Thanks!

[sunny_talwar]

May be with KeepChar(FieldName, '0123456789') if everything else would be text?

[crystles]

I have tried that, but the problem is, the number that could be in the CommentsField is in the ItemsField so it looks like

KeepChar(CommentsField, ItemsField)

and that just returns a null.

I wanted to try to do a WildMatch(), to match what is in the ItemField to the CommentsField, but that did not work either.

[sunny_talwar]

Try something like this:

Table:

LOAD *,

  Left(SubField(CommentField, SubField(CommentField, ItemField, 1), 2), 😎 as Test;

LOAD * Inline [

ItemField, CommentField

12345678, Missing: 12345678

12345678, ItemName 12345678 Item description

12345678, Item description 12345678

];

[rbecher · Accepted solution]

How does look the item number in real? Is it an alphanumeric value?

If so, you could load a mapping table with all item numbers and do a MapSubstring() to mark the item number for a cut away with Textbetween:

Items:
LOAD * INLINE [
    ItemNo
    A123
    A234
    A345
    B45678
    B56789
    C678
    C7890
];

ItemMap:
Mapping LOAD ItemNo, '#' & ItemNo & '#' as Subst
Resident Items;

data:
LOAD Textbetween(MapSubString('ItemMap',Comments), '#', '#') as ItemsFromComment;
LOAD * INLINE [
    Comments
    Missing: A123
    ItemName B45678 Item description
    Item description C678
];

- Ralf

[crystles]

Yes, I was trying to do something like this in the application but I see it it better to do it in the script.

The only problem I have is that the two tables don't match. I suppose I should have mentioned that the fields (Item and Comment) come from different tables.

So if they do not match then I can't link them the Item number?

[rbecher]

Well, at least you can pull out item numbers you already have from the other source. But even I do not know how to pull something out you don't know..

[crystles]

I figured out why they didn't match.

The Item numbers were saved as Text but were actually only numbers. BUT they had leading zeros in some of the numbers (ex: 00012345, 00000123). I just simply but a number function around the first item in the first table and now they all match. Thank you for solving this problem for me!

Here's my code.

Items:

LOAD

     Num(ITEM,00000000) as Test_Item,

     [ITEM DESCRIPTION] as Test_ItemDesc

FROM ItemTable

ItemMap:

Mapping LOAD Test_Item, '#' & Test_Item &  '#' as MapSubstring

Resident Items;

Data:

LOAD Textbetween(MapSubString('ItemMap', Test_Comments), '#', '#') as ItemsFromComment;

LOAD

     Comments as Test_Comments

FROM DataTable

[rbecher]

I think you rather should load the item numbers with: Text(ITEM)

[rbecher]

..well it depends how users filling them in into the comments. If the omit leading zeros sometimes you probabyl need two different mappings.