Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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
May be with KeepChar(FieldName, '0123456789') if everything else would be text?
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.
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
];
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
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?
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..
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
I think you rather should load the item numbers with: Text(ITEM)
..well it depends how users filling them in into the comments. If the omit leading zeros sometimes you probabyl need two different mappings.