Defining a dimension

[Anonymous]

Hi all,

I'm trying to divide a field by 4 groups. If the field value doesn't fit any group, it shall continue using the original description name. This one will be a 'group' that only have 1 value.

=if(match(AllocTransfOrigMarketCd,'14','15','93'),'AU+CE+CH',

  if(match(AllocTransfOrigMarketCd,'22','23'),'BL+HL+NE',

        if(match(AllocTransfOrigMarketCd,'999','AC','AM','AP','OF','27','.','SET','30','PK','73','GPS','SP','SCO'),'Other',

            if(match(AllocTransfOrigMarketCd,'91','92','92Z','92L','92M','92O','91Z','91L','91M','91A','91O','92A'),'Overseas',

                if(match(AllocTransfOrigMarketCd,'17','28'),'UK+IR',

                    AllocTransfOrigMktDescrip)))))

What happens is that qlik sense gets confuse and, for example, if AllocTransfOrigMarketCd= 10, it should be placed in the group of a single value called AllocTransfOrigMktDescrip=PORTUGAL but, instead, currently this group may aggregate much more AllocTransfOrigMarketCd values...

you may see an extract of the division on the image attached.

Do you have any idea what i might be doing wrong?

Thank you!

[YoussefBelloum]

Hi,

I did not understand what you mean by this:

What happens is that qlik sense gets confuse and, for example, if AllocTransfOrigMarketCd= 10, it should be placed in the group of a single value called AllocTransfOrigMktDescrip=PORTUGAL but, instead, currently this group may aggregate much more AllocTransfOrigMarketCd values...

what is the expected values when the source field =10, it should be Portugal because you didn't mention the value 10 on your if(match())....

can you elaborate more please ?

[Anonymous]

Thank you youssef.

Let's say this dimension is called Office. If AllocTransfOrigMarketCd is not mentioned on any match function of Office definition, the name of the group of that field in the dimension Office should be correspondent AllocTransfOrigMktDescrip.

For example, as AllocTransfOrigMarketCd= 10  is not mentioned, it should be include in a group called PORTUGAL

[YoussefBelloum]

OK that's what I thought.  but what is your actual output ? what is wrong ?

[Anonymous]

[Anonymous]

For examples, AllocTransfOrigMktDescrip=GERMANY is including AllocTransfOrigMarketCd = 13,14,15 instead of only include 13 (wich is the marketcd that corresponds to mktdescrip=germany)