Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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