topic Re: creating new dimension using existing data in QlikView

creating new dimension using existing data

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: creating new dimension using existing data

rrsrini2907 — Sat, 07 May 2016 16:36:54 GMT

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.

Re: creating new dimension using existing data

MindaugasBacius — Sat, 07 May 2016 16:46:44 GMT

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

sunny_talwar — Sat, 07 May 2016 16:48:48 GMT

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

Re: creating new dimension using existing data

Anonymous — Sun, 08 May 2016 07:58:06 GMT

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

Re: creating new dimension using existing data

Mon, 09 May 2016 06:14:46 GMT

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

Re: creating new dimension using existing data

MindaugasBacius — Mon, 09 May 2016 06:19:14 GMT

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

sunny_talwar — Mon, 09 May 2016 06:21:56 GMT

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