Merging two dimensions into one

[#1]

Hi - I have two dimensions in the base data Excel file,and I want to merge them so they both display as one name.

Dimensions I have:

North West

North East

Dimension  I want:

North

Is there a way to do this in Qlikview rather apart from going into the Excel files and fixing the names there? Kindly help!

[#2]

Hi, even this does not work.

The name of the dimension is "Region" and within there, there are categories

Assurance North West & Assurance North East

I want to combine them so that they show as "Assurance North".

When I tried to use the code you provided, it said "Field not found <Assurance North West>".

Any help would be great.

[nizamsha]

you have 2 field right

North West

North East

where u r getting this field .according u. u r getting in a excel so load  first excel again  after that change the field name northwest as north

again load the another excel in that u have north east i think after loading  that change the north east field as north

if u load the script if will auto concatencate then  u have only one field as north .i hope its enough

LOAD

     [North West] as North

FROM

[Aura Data - For Analysis - 11.9.xlsx]

(ooxml, embedded labels, table is [active files query]);

LOAD

     [North East] as North

FROM

[Aura Data - For Analysis - 11.9.xlsx]

(ooxml, embedded labels, table is [active files query]);

[#4]

LOAD

     If ([Region]='Assurance North West' or [Region]='Assurance North East', 'Assurance North', [Region]) as [Region]

Using the IF-statement you can look for the North West and North East- string.

When found the String 'Assurance North' will be used as Region.

[#5]

The load script for this file is:

Directory;

LOAD [Engagement name],

     [Period Ending],

     Office,

    Region,

     [Audit Unit name],

     [Risk name],

     [EGA name],

     [Evidence type],

     [EGA Status],

     [Content EGA Id],

     [EGA Library Source],

     [Preparer Notes Address],

     [Reviewer Notes Address],

     [Reviewer Grade],

     [Preparer Grade]

FROM

[Aura Data - For Analysis - 11.9.xlsx]

(ooxml, embedded labels, table is [active files query]);

Within the column labelled "Region" in the Excel file, I have many different regions. 2 of these are called "Assurance North East" and "Assurance North West".

I want to merge them both into "Assurance North".

Regards,

[#6]

Hi - thanks for your reply but I get this error when I pasted your script in the Edit Script section and did a reload:

"

Script line error:

If ([Region]='Assurance North West' or [Region]='Assurance North East', 'Assurance North', [Region]) as [Region]"

[#7]

Hi

Try if statement in your load.it can help you.

[#8]

Hi I just tried to paste this in the Edit Script section and do a reload but I got the following Error message:

"If ([Region]='Assurance North West' or [Region]='Assurance North East', 'Assurance North', [Region]) as [Region]"

Any ideas on how to resolve this?

[nizamsha]

aa:

load* Inline [

region

northeast

northwest

northnorth

northsouth

] ;

load region ,if(region='northeast' or region='northwest','north') as region1

Resident aa;

[#10]

You'll have to end the line with a comma.

As it is not the last LOAD-statement in your script.

After that it should work.

[#11]

I did that and this got  rid of the error message, however, I can still see "Assurance North West" and "Assurance North East" in the Region Listbox instead of just one for "Assurance North".