Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jeremeywise
Contributor
Contributor

Dimention value - If not in other list

Picking Qlik Sense back up after being away for a year..

Trying to make a simple pie chart.. 

I have two spreadsheets in Qlik Sense:

FruitTable1CostQty
apples11
pears424
watermelon562
grapes266
oranges16142
cherries345576
watermelon12313
tomato23769

BadFruitBadFruitCatagory
tomatovegetable
eggplantweed
watermelonvine

Dim1 (field)

=(FruitTable1  if not listed in BadFruit)

AltDim1 (field)

=(FruitTable2 if listed in BadFruit where BadFruitCatagory="weed")

AltDim2 (field)

=(FruitTable2 if listed in BadFruit where BadFruitCatagory="vine")

Goal is to end up listing "how many <let user select fruit and by type> of each catgory are sold and later on in diferent pie chart at that cost.

Meanwhile I will slog through the training videos again to refresh.

3 Replies
ogautier62
Specialist II
Specialist II

Hi,

The best will be to make a link between two tables in script

and have same name for fruit with a flag for bad fruit

with this model :

for case 1:

if (fruittable1 <> badfruit, fruittable1) as dimension

but it's not efficient

regards

jeremeywise
Contributor
Contributor
Author

My goal is to almost build a master dimension list. The data I get does not have a category column so I have to find a way to create a dimension: (I would have t set of four or more master dimensions)

Ex:  MasterDimBadFruit_vegitable

IF FruitTable1 = 'tomato' or 'artichoke'


Ex:  MasterDimBadFruit_vine

IF FruitTable1 = 'watermelon' or 'cantaloupe'

etc.

But that formula is not a way to create a dimension and invalid as it lacks a "then" .   I want to write a formula that creates a master dimension based on a list.

I tried to create the second spreadsheet as 'master table' as I could teach sales to easily build and save by tab 'categories' they want to group but like you said... how this then can be set as a dimension value when searching the main database is what I don't know how to do.

Most categories they will want to group are four of five parts.. others are hundred....hence the list vs VERY long string of 'or...'

ogautier62
Specialist II
Specialist II

for example :

Fruit            Category

tomato        badfruit

tomato        redfruit

eggpant     badfruit

strawberry goodfruit

this table could be maintained by users (field Fruit must be linked with table 1 same name)

user select then category(s) he wants

Dimension is Fruit