Finding Multiple Pattern values

[dusasuman]

Hi Community,

I have a "Description" fields contains the description of different items. I need to generate a field with only the items separating by commas. For example: consider Description field has a value "Three Mangos and two Apples" then in my output field should get "Mango,Apples" as a value. Like a flag showing all fruits mentioned in that particular record.

I have also attached the sample data in the excel file. there are two sheets in this file "Input" sheet shows my input data need to be loaded in qlikview. And out put shows what exactly my output is..

Please help me out.

Thanks in Advance..

Suman.

[Gysbert_Wassenaar]

You will need a table with the items you want to match. If you have that then you can do something like this:

Items:

LOAD

  Item

FROM

  [ExelFileWithItems.xlsx]

  (ooxml, embedded labels, table is Keywords)

  ;

Data:

LOAD

  ID,

  Concat(DescriptionWord,', ') as List

WHERE

  Exists(Item,DescriptionWord)

GROUP BY

  ID

  ;

LOAD

  ID,

  PurgeChar(SubField(Description,' '),',.-') As DescriptionWord

FROM

  [Patterns.xlsx]

  (ooxml, embedded labels, table is Input)

  ;

[dusasuman]

Thanks for your reply.. Yes, the same items will repeat over all the records. Assume.. there will not be any item will be added in future. It is fine with creating manually one file with the items list. However the description values may varies with holding three or more combination of items..

Lets consider we have that items file.

I appreciate if you create that items excel and generate the solution in a qlikview file..

[dusasuman]

Thanks for the solution. It really works for 80% of descriptions. Few items will may have empty space with in the item for example: "Green Beens".. Is there any more optimal solution.. I have this item in the list but not mentioned in the sample file..

[MarcoWedel · Accepted solution]

Hi,

one solution to also extract item names including spaces could be:

mapItems:
Mapping LOAD *, '@start@'&Item&'@end@'
Inline [
Item
Apple
Avacado
Banana
Cabbage
Carrot
Corn
Cucumber
Grapes
Green Beens
Lemon
Mango
Orange
Tomato
];

tabDescriptions:
LOAD * Inline [
ID Description
101 Mango contains Vitamin A, B1, B2.
102 Banana  Vitamin A, B2, B6 and C. Same with Apple
103 Lemon good for skin and acidity. Similar for Orange as well.
104 Folate vitamins - Tomato. Same with Avacado
105 Vitamin B6- Cabbage and Carrot.
106 Corn, Cucumber contains Vitamin B6
107 Folate vitamins - Tomato. Same with Avacado
108 Vitamin B6- Cabbage and Carrot.
109 Water soluble content - Grapes and Apples
110 Mango contains Vitamin A, B1, B2.
111 Banana  Vitamin A, B2, B6 and C. Same with Apple
112 Lemon good for skin and acidity. Similar for Orange as well.
113 Folate vitamins - Tomato. Same with Avacado
114 Vitamin B6- Cabbage and Carrot.
115 Corn, Cucumber contains Vitamin B6
116 Added Green Beens as Item including spaces
] (delimiter is '\t');

tabItems:
LOAD ID,
    TextBetween(MapSubString('mapItems',Description),'@start@','@end@',IterNo()) as Item
Resident tabDescriptions
While IterNo() <= SubStringCount(MapSubString('mapItems',Description),'@start@');

Left Join (tabDescriptions)
LOAD ID,
    Concat(DISTINCT Item, ', ') as Items
Resident tabItems
Group By ID;

You could shorten this script e.g. using preceding load aggregation like proposed by Gysbert, but I think creating a separate field for individual items delivers a more dynamic and flexible analysis:

hope this helps

regards

Marco

[dusasuman]

Great Solution !!..

Thanks for your help..

[MarcoWedel]

‌you're welcome.

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco