Dimention value - If not in other list

[Anonymous]

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:

FruitTable1	Cost	Qty
apples	1	1
pears	4	24
watermelon	5	62
grapes	26	6
oranges	16	142
cherries	345	576
watermelon	123	13
tomato	23	769

BadFruit	BadFruitCatagory
tomato	vegetable
eggplant	weed
watermelon	vine

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.

[ogautier62]

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

[Anonymous]

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]

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