How to group words separated by commas into one field within the load script

[#1]

Hello,

I have an excel document with multiple columns. I have multiple words in the cells separated by commas so I have used trim(lower(subfield([Primary Cat],','))) as Category. Let's say some of the fields within Primary Cat are words like cat, dog, bird, rabbit (separated by commas). How can I, within Qlikview, make an overall category 'Animal' that says Animall = cat, dog, bird, rabbit. Is this done in the load script? I think this is pretty basic, but i am not sure how to enter it into the load script. Thank you.

[swuehl · Accepted solution]

Yes, I would do this in your load script. You could start with an inline table

OverallCat:

LOAD * INLINE [

Overall Category, Category

Animal, cat

Animal, dog

Animal, bird

Animal, rabbit

];

This will create a new table, CategoryCat, with a new field Overall Category with one value, Animal. This value is linked to the appropriate values of field Category (which originated from your excel). Here, Category values must match exactely to be linked to Animal (e.g. take care of case).

Instead of using an additional table, you could also map your Data into the existing table, creating an additional field or replacing the old one (I show how to create a new one here):

MappingCat:

MAPPING LOAD * INLINE [

Overall Category, Category

Animal, cat

Animal, dog

Animal, bird

Animal, rabbit

];

LOAD

ApplyMap('MappingCat', Category, 'No Animal') as [Overall Category];

LOAD

trim(lower(subfield([Primary Cat],','))) as Category

from YourExel.xls;

Hope this helps,

Stefan

[#3]

The first created a field in the multibox, however it does not link it to my other data. It creates the 'Overall Category' Field and when you drop it down it has the new category CPG, however it does not link anything- it doesn't pull from my other data, and everything is blank.

This is in my load script:

LOAD

......

Company,

Award,

Solution

Industries,

trim(lower(subfield(Industries,','))) as Industry,

.......

I want to create a category called CPG and group a bunch of industries in this one field called CPG. So this is what I put in the load script:

LOAD

OverallCat:

LOAD * INLINE [

Overall Category, Category

CPG,  consumer goods

CPG, consumer goods manufacturing

CPG, consumer products

CPG, consumer electronics

];

[swuehl]

A link between tables is established by using the same field name. Thus, in your OverallCat inline table, you need to rename Category to Industry (or vice versa in your original table load).

LOAD

OverallCat:

LOAD * INLINE [

Overall Category, Industry

CPG,  consumer goods

CPG, consumer goods manufacturing

CPG, consumer products

CPG, consumer electronics

];

[#5]

I tried this. But when I click on CPG all my charts and tables go blank. It is not correlating 'CPG' to the companies that are in consumer goods, or consumer goods manufacturing etc. I'm a little confused. Maybe I am still not doing it correctly . Thank you for your help

[swuehl]

Please check  on table viewer (CTRL-T), that the tables are correctly linked via field Industry.

Then, the actual values must match, so please check also that the values are identically spelled in both tables, OverallCat and your original load table (this does not mean that your inline table must contain all different values, but those who are contained in both tables must be written exactely the same way).

You could also create a list box of field Industry and have a look at the values. Do you see duplicates / very small differences / words both written in lower and upper case?

[swuehl]

Have you checked the spelling? Your other Industry values seem to be lower case...

[#8]

that is because of the trim(lower(subfield.... it gets rid of lower and upper case so in case there was the same word but different caps, it would still recognize it as the same word

[swuehl]

Yes, but your value from OverallCat table (CPG selected) seem to be using Upper case?

[#10]

It seems to work when i use all lowercase. THank you so much for your time and patience helping me!