topic Re: Finding Multiple Pattern values in QlikView

Finding Multiple Pattern values

dusasuman — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Finding Multiple Pattern values

Gysbert_Wassenaar — Wed, 18 Jan 2017 17:58:31 GMT

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

;

LOAD

ID,

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

FROM

[Patterns.xlsx]

(ooxml, embedded labels, table is Input)

;

Re: Finding Multiple Pattern values

dusasuman — Wed, 18 Jan 2017 18:14:26 GMT

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

Re: Finding Multiple Pattern values

dusasuman — Wed, 18 Jan 2017 18:56:26 GMT

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

Re: Finding Multiple Pattern values

MarcoWedel — Thu, 19 Jan 2017 00:37:50 GMT

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

Re: Finding Multiple Pattern values

dusasuman — Thu, 19 Jan 2017 08:48:01 GMT

Great Solution !!..

Thanks for your help..

Re: Finding Multiple Pattern values

MarcoWedel — Thu, 19 Jan 2017 23:00:46 GMT

‌you're welcome.

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco