topic Re: Calculated Dimension in QlikView

Calculated Dimension

MEJ — Wed, 25 Nov 2020 16:16:04 GMT

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 '))

Re: Calculated Dimension

Vegar — Mon, 24 Jun 2019 15:12:30 GMT

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

Re: Calculated Dimension

MEJ — Mon, 24 Jun 2019 15:18:13 GMT

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

Re: Calculated Dimension

Vegar — Mon, 24 Jun 2019 17:36:05 GMT

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

Re: Calculated Dimension

MEJ — Tue, 25 Jun 2019 11:24:11 GMT

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?

Re: Calculated Dimension

Vegar — Tue, 25 Jun 2019 19:10:05 GMT

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/StringFunctions/SubField.htm