Multiple dimension values for a single record

[#1]

Hi Everyone,

I have run into an interesting issue and am looking for suggestions.

I was asked to split some numerical value (measure1) into various categories in a new dimension (food category) based on the value of some other dimensions for that record (dimension2 and 3)

I used a nested if statement in the script

e.g.

if(dimension2='category1' AND mid(dimension3,1,1)='Y','Potato', 
if(dimension2='category1' AND mid(dimension3,2,1)='Y','Banana',
if(dimension2='category1' AND mid(dimension3,3,1)='Y','Apple',
if(dimension2='category1' AND mid(dimension3,4,1)='Y','Orange', 
if(dimension2='category1' AND mid(dimension3,5,1)='Y','Pear', 
if(dimension2='category1' AND mid(dimension3,6,1)='Y','Grapefruit', ''))))))  as  [Fruit Category];

Under the assumption that each record could be only a potato, or only a banana, or only an apple, etc.

so I have a table built in the dashboard that gives measure totals for each of these 'fruit categories'

However my end user has just informed me that if some record 'X' has mid(dimension3,1,1) = Y AND also has mid(dimension3,2,1) = Y the this record should be counted for both potato and bananas and should be appear in the total for both potatos and bananas in the table.

This is a problem because for any record loaded, it will first check if it fits the potato definition and if it does it will be assigned the dimensional value for potato and never reach the portion checking for bananas even though it may also be a banana (Schrodinger's fruit).

How can I go about accommodating a case where a single record is supposed to have multiple possible values for the same dimension? I'd like to avoid duplication of records if possible... but that doesn't seem likely here.

Thanks!

[Oleg_Troyansky · Accepted solution]

Based on your description, the [Fruit Category] cannot be presented as an attribute for each Dim2 and Dim3 (as you initially assumed), but might have multiple values for each one of those.

What I would do (in your load script) is create a Combo Key field that contains both Dim2 and Dim3 and then load a separate table with the relation between the Combo Key and the "Fruit Category". In order to allow multiple results, I wouldn't use the nested IF statement (which only returns a single results). I would use multiple loads with the corresponding WHERE conditions. For example:

FruitCategories:

LOAD Distinct

     ComboKey,

     'Potato' as [Fruit Category]

RESIDENT

     Fruits

WHERE

     dimension2='category1' AND mid(dimension3,1,1)='Y'

;

This way, any possible combinations will get captured and the corresponding fruits will get included in multiple fruit categories.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

[swuehl]

I think you would need to create multiple records, either duplicating your fact table record (containing Measure1) or creating a key for the fact records and using a link table. Depends on how you want to handle your Measure1 aggregations.

[Digvijay_Singh]

What if you create one new category 'Potato_Banana and check at the top -

if(dimension2='category1' AND mid(dimension3,1,2)='YY','Potato_Banana', 

I think it is possible to handle this category in both Potato and Banana total at front end somehow using string matching. May it some performance though.

[johnw]

You could build your supplementary table like this, assuming your fact table has an ID. See attached.

Fruit:
CROSSTABLE (Fruit,Flag)
LOAD
ID
,mid(dimension3,1,1) as Potato
,mid(dimension3,2,1) as Banana
,mid(dimension3,3,1) as Apple
,mid(dimension3,4,1) as Orange
,mid(dimension3,5,1) as Pear
,mid(dimension3,6,1) as Grapefruit
RESIDENT Data
WHERE dimension2='category1'
;
INNER JOIN (Fruit)
LOAD 'Y' as Flag
AUTOGENERATE 1
;
DROP FIELD Flag;

[johnw]

If it's acceptable to just list the fruits in a single field, you can avoid the extra table you wanted to avoid, but I see no reason to avoid another table. (Edit: Oops, check the dimension 2 value too, but I'm too lazy to go back and edit.)

,if(mid(dimension3,1,1)='Y','Potato ')
&if(mid(dimension3,2,1)='Y','Banana ')
&if(mid(dimension3,3,1)='Y','Apple ')
&if(mid(dimension3,4,1)='Y','Orange ')
&if(mid(dimension3,5,1)='Y','Pear ')
&if(mid(dimension3,6,1)='Y','Grapefruit ') as Fruits

[#7]

Spot on..Thanks for the clear explanation.