Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
];
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
];
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
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?
Have you checked the spelling? Your other Industry values seem to be lower case...
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
Yes, but your value from OverallCat table (CPG selected) seem to be using Upper case?
It seems to work when i use all lowercase. THank you so much for your time and patience helping me!