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!

[Anonymous]

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

[#3]

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

[v_iyyappan]

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,

[Anonymous]

Hi Osama,

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

[#6]

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

[#7]

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?

[#8]

Hi where do I add this?

[v_iyyappan]

Hi

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,

[CELAMBARASAN]

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?