Calculated Dimension

[MEJ]

Having some trouble getting this right. I have used Calculated dimensions before, but this dimension I am using could have several dimensions/attributes inside the field.

Let's say I would like to create a calculated dimension based on the below attributes. Each "word" represents one attribute for the particular order. So, the first row has three attributes on the order. MEDIA, ATC, and HH (never mind what they mean). The 5 remaining also have MEDIA as an attribute. 

MEDIA ATC HH
MEDIA ATC HH VR
MEDIA ATC HH XFLO VR
MEDIA ATC HHR VR
MEDIA ATC HHR XFLO VR
MEDIA ATC PT ADB HHR VR B2B

So I would like to return the following number for each attribute:

ADB: 1

ATC: 6

B2B: 1

HH: 3

HHR: 3

MEDIA: 6

PT: 1

VR: 5

XFLO: 2

I tried the following script, but it didn't work as I wanted:

=(if(wildmatch(Addition,'*ADB*'),'ADB '))
& (if(wildmatch(Addition,'*ATC*'),'ATC '))
& (if(wildmatch(Addition,'*B2B*'),'B2B '))
& (if(wildmatch(Addition,'*HH*'),'HH '))
& (if(wildmatch(Addition,'*HHR*'),'HHR '))
& (if(wildmatch(Addition,'*MEDIA*'),'MEDIA '))
& (if(wildmatch(Addition,'*PT*'),'PT '))
& (if(wildmatch(Addition,'*VR*'),'VR '))
& (if(wildmatch(Addition,'*XFLO*'),'XFLO '))

[Vegar]

An alternative solution is to adjust your datamodel in such a way that you have the words as a real dimension. Like in the script below.

Data:
LOAD Words, Words as %word inline [
Words
MEDIA ATC HH
MEDIA ATC HH VR
MEDIA ATC HH XFLO VR
MEDIA ATC HHR VR
MEDIA ATC HHR XFLO VR
MEDIA ATC PT ADB HHR VR B2B];


Words:
LOAD 
	%word,
	subfield(Words, ' ') as Word
RESIDENT Data;
exit script

[MEJ]

Thank you.

Now I only gave an extract of combinations. There could be many hundreds of combinations like this, and there will never be a one size fits all solution. As I have 21 attributes, which means there could be theoretically hundreds of thousands of combinations, maybe millions (haven't made the calculation of total possible combinations). It must be a solution where I don't have to pre-define possible combinations. Would this solution handle that?

/Mathias

[Vegar]

Yes it will handle more and fewer words.

I believe that this approach will have a better prestanda than the
suggested calculated dimension approach.

Vegar

[MEJ]

I will see if I can understand how to insert that script into my load script and make it work. 

What does the % sign do?

Could you explain in words that the script is doing? 

[Vegar]

The % does nothing, it is just as any other character, I usually put % in
front of field names that are linking tables together.

What I do is creating a additional dimension linked to the field containing
the word collections. In the new dimension table for each word collection I
create a row per word using SubFields() without a third parameter.

Read about SubFields here:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Strin...