topic Re: Merging two dimensions into one in QlikView

Merging two dimensions into one

Fri, 20 Nov 2020 18:02:30 GMT

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!

Re: Merging two dimensions into one

Anonymous — Thu, 12 Sep 2013 16:54:46 GMT

Hello,

With a simple concatenation it Works:

Tab:

LOAD Dim1,
     [North East] as North,
     Exp1
FROM
ExcelFile.xlsx
(ooxml, embedded labels, table is Plan1);

LOAD Dim1,
     [West East] as North,
     Exp1
FROM
ExcelFile.xlsx
(ooxml, embedded labels, table is Plan1);

Re: Merging two dimensions into one

Thu, 12 Sep 2013 17:23:00 GMT

What is Dim1?

I entered this code in the edit script and it didnt work. The error message said "Couldn't find dim1"

LOAD Dim1,

[Assurance North West] as North,

Exp1

FROM

[Aura Data - For Analysis - 11.9.xlsx]

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

LOAD Dim1,

[Assurance North East] as North,

Exp1

FROM

[Aura Data - For Analysis - 11.9.xlsx]

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

Re: Merging two dimensions into one

v_iyyappan — Thu, 12 Sep 2013 17:39:22 GMT

Hi,

Two excel files containing same fields means using concatenate function.

Try like this:

LOAD

[Assurance North West] as North

FROM

[Aura Data - For Analysis - 11.9.xlsx]

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

LOAD

[Assurance North East] as North

FROM

[Aura Data - For Analysis - 11.9.xlsx]

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

Regards,

Re: Merging two dimensions into one

Anonymous — Thu, 12 Sep 2013 17:46:16 GMT

Hi Osama,

Dim1 and Exp1 are just example fields. Use your fields instead, but keep the North West and North East like I showed.

Re: Merging two dimensions into one

Thu, 12 Sep 2013 17:51:20 GMT

You could also do it after import into qv using the subfield function. For example, you could edit the dimensions with the following:

subfield([North West], ' ', 1)

subfield([North East], ' ', 1)

They would then return North.

Hope that helps.

Matt

Re: Merging two dimensions into one

Fri, 13 Sep 2013 16:17:16 GMT

Hi Thanks for your reply, but i get this error message afterI pasted your code in the Edit script section:

Field not found - <Assurance North West>

LOAD

[Assurance North West] as North

FROM

[Aura Data - For Analysis - 11.9.xlsx]

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

Can you tell me what the issue would be?

Re: Merging two dimensions into one

Fri, 13 Sep 2013 16:17:30 GMT

Hi where do I add this?

Re: Merging two dimensions into one

v_iyyappan — Fri, 13 Sep 2013 19:28:35 GMT

Try like this

LOAD

subfield([North East], ' ' ,1) As North

FROM

ExcelFile.xlsx

(ooxml, embedded labels, table is Plan1);

LOAD

subfield([North West],' ',1) AS North

FROM

ExcelFile.xlsx

(ooxml, embedded labels, table is Plan1);

Where Plan1 --> Ur excel file name

If you are using more than one fields means that is use script also.

Regards,

Re: Merging two dimensions into one

CELAMBARASAN — Fri, 13 Sep 2013 19:38:38 GMT

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]);

If this is not working could you post the script you have for loading excel file?

Re: Merging two dimensions into one

Mon, 16 Sep 2013 10:40:06 GMT

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.

Re: Merging two dimensions into one

nizamsha — Mon, 16 Sep 2013 10:52:35 GMT

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]);

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:00:35 GMT

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.

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:01:05 GMT

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,

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:09:25 GMT

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]"

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:09:44 GMT

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

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:11:19 GMT

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?

Re: Merging two dimensions into one

nizamsha — Mon, 16 Sep 2013 11:13:11 GMT

aa:

load* Inline [

region

northeast

northwest

northnorth

northsouth

] ;

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

Resident aa;

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:14:18 GMT

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.

Re: Merging two dimensions into one

Mon, 16 Sep 2013 11:20:19 GMT

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".