Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crystles
Partner - Creator III
Partner - Creator III

Pull 8 Digit value out of Comment field

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!

1 Solution

Accepted Solutions
rbecher
MVP
MVP

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

Astrato.io Head of R&D

View solution in original post

10 Replies
sunny_talwar

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

crystles
Partner - Creator III
Partner - Creator III
Author

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
MVP
MVP

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

Astrato.io Head of R&D
crystles
Partner - Creator III
Partner - Creator III
Author

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
MVP
MVP

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..

Astrato.io Head of R&D
crystles
Partner - Creator III
Partner - Creator III
Author

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
MVP
MVP

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

Astrato.io Head of R&D
rbecher
MVP
MVP

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

Astrato.io Head of R&D