Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

creating new dimension using existing data

Dear All,

I have a database with few dimensions, among them the ACCOUNT and DESCRIPTION. Among accounts I have those starting from 4xx and those starting from 5xx. the 5xx contain in their string the 4xx ones. As per attachment, there is an account no 401-101 and the 501-00214-401-101.

I would like to create a new dimension in the LOAD script (501-Group), mapping the names of 4xx account to appropriate 5xx ones (and only to 5xx ones, in case of other accounts this dimension should be blank. I did what I want to get in excel, but don't know QV formulas enough to repeat it. could you please help?

Thanks in advance

draszor

7 Replies
Highlighted
rrsrini2907
Contributor

Re: creating new dimension using existing data

Hi,

The same lookup functionality is available in QlikView as Mapping Load and ApplyMap.

Attached the below script.

BaseData:

LOAD ACCOUNT,

     Description

FROM [..\Downloads\new_dimension.xlsx] (ooxml, embedded labels, table is base);

MapBase:

MAPPING LOAD ACCOUNT,

     Description

Resident BaseData Where Left(ACCOUNT,1) = 4;

Concatenate (BaseData)

LOAD ACCOUNT,

     ApplyMap ('MapBase',RIGHT(ACCOUNT,7),Null()) as [501-GROUP]

FROM [..\Downloads\new_dimension.xlsx] (ooxml, embedded labels, table is Mapping);

Regards,

Srini.

MindaugasBacius
Valued Contributor II

Re: creating new dimension using existing data

According to you issue your script should look like this:

mapping_account:

MAPPING LOAD Account,

     Description

FROM

(ooxml, embedded labels, table is Sheet1)

Where left(Account, 1) = 4;

Data:

LOAD Account,

     ApplyMap('mapping_account', Right(Account, 7), ':') as Description

FROM

(ooxml, embedded labels, table is Sheet1)

Where left(Account, 1) = 5;

See the attachments.

Re: creating new dimension using existing data

May be this?

MappingTable:

Mapping

LOAD ACCOUNT,

    Description

FROM

new_dimension.xlsx

(ooxml, embedded labels, table is base);

Table:

LOAD ACCOUNT,

    ApplyMap('MappingTable', Right(ACCOUNT, 7), Null()) as Description

FROM

new_dimension.xlsx

(ooxml, embedded labels, table is Mapping);


Capture.PNG

vsudhakar
Contributor III

Re: creating new dimension using existing data

by using apply map to give new dimension name or to give load field name as new name resident table name also to get the new dimension

Not applicable

Re: creating new dimension using existing data

Thanks a lot for your help.

So, it's just to remember that what vlookup() does in excel, Mapping load and ApplyMap() formula does in QV.

I was successful with my task, bus would still have an additional question.

When having Mapping table in a separate excel file, everything works great. The Mapping load has its simple construction of Mapping Load ... From file.path.

However, the original database is loaded from ORACLE database, using the following construction:

BasicDataLoad:

Load *,

here I create few additional dimensions based on the ones sitting in database...

;

SQL Select *

From myDatabase

In this BasicDataLoad statement all the dimensions needed to do the mapping are loaded. question - is there a way that I use in my Mapping Load (that goes before this BasicDataLoad statement in the script) a reference to this BasecDataLoad as data source, so that I avoid keeping separate excel? I was trying the followng construction:

Mapping Load

...

From BasicDataLoad

this does not work.

thanks for any feedback

Robert

MindaugasBacius
Valued Contributor II

Re: creating new dimension using existing data

A mapping table consists of two columns, the first containing comparison values and the second containing the desired mapping values.

In case you have more than one field to lookup you should use Join statement.

Re: creating new dimension using existing data

Robert -

Mapping Load only works with two fields at a time (one which is getting mapped from and the other which is getting mapped from). There are ways to do multiple mapping from one mapping load, but the syntax get a little messy where you need to create a concatenated list of all mapped to field in the mapping load and then use ApplyMap with SubField function. I would argue creating multiple mapping tables for 3-4 different mappings because it is easy to understand. Beyond that you might want to consider doing a join. Join is an alternative to mapping. Between join and applymap, applymaps tends to be considered a little better. Don't join - use Applymap instead

With regards to mapping from Oracle, you can def. do a mapping load from any database that you may have. Again you need to only have two fields in your mapping load table.

I hope this answers all your questions. Let us know if you still have any questions.

Best,

Sunny

Community Browser