topic Re: Mapping Load from Excel, ApplyMap from Excel-Table in QlikView

Mapping Load from Excel, ApplyMap from Excel-Table

Mon, 16 Nov 2015 10:21:03 GMT

Hi everybody,

I have in my Qlikview-Application several [Mapping Load * Inline].

Due to the fact, that we have lots of Codes with Descriptions I created an Excel-File with all of them

Now I want to load them and Change all Applymaps to that source.

How can I do load a table from Excel as MappingLoad and what should I do to get a applymap to work?

Thanks for you help in advance.

Re: Mapping Load from Excel, ApplyMap from Excel-Table

Anonymous — Mon, 16 Nov 2015 10:25:17 GMT

See the following threads, how to use applymap:

quick applymap example

Mapping Load - Apply Map Question

ApplyMap Mith Multipe fields syntax

Re: Mapping Load from Excel, ApplyMap from Excel-Table

rubenmarin — Mon, 16 Nov 2015 10:29:00 GMT

Hi Christian, you can use the assitant to retrieve the data from Excel and creates the LOAD, you only need to add the 'Mapping' word before the 'LOAD' to create the mapping.

One issue you can found is that the numbers comes in a different format from the one you will look in ApplyMap(), in this case use Num() or Num#() to load with the desired format.

You can use it as any other mapping table:

Applymap(Map_Name, ValueToSearch, DefaultValue) as FieldName

Re: Mapping Load from Excel, ApplyMap from Excel-Table

tresB — Mon, 16 Nov 2015 10:31:53 GMT

You have to just change the source. Instead of inline load, load it from excel source, like:

<maptable>:

Mapping Load

Number_Code,

Number_Description

From <Excel path>;

Re: Mapping Load from Excel, ApplyMap from Excel-Table

tamilarasu — Mon, 16 Nov 2015 10:32:59 GMT

Hi Christian,

Check the below attachment. Adapt it as per your requirement.

Re: Mapping Load from Excel, ApplyMap from Excel-Table

settu_periasamy — Mon, 16 Nov 2015 10:33:14 GMT

Hi,

Try this..

Load Your Excel file as usual with Prefix of "Mapping".

Use the MAP FieldName Using Mapping_Table

MAP_DESC:

Mapping LOAD

Number_Code,

Number_Description

FROM

MAP_SOURCE.xlsx

(ooxml, embedded labels, table is Sheet3);

MAP FieldList using MAP_DESC;

Re: Mapping Load from Excel, ApplyMap from Excel-Table

Mon, 16 Nov 2015 10:35:52 GMT

Hi Balraj,

thanks for the quick answer. It helped a lot

But one addtional question. Why is no table created in Qlikview?

I thought with the Code below, I would get a table called 'Map_Number_Codes'.

Map_Number_Codes:
mapping LOAD Distinct
Number_Code,
Number_Description

FROM

(ooxml, embedded labels, table is Tabelle1);

Do you know the reason, why such a table is not in Qlikview after loading?

I Need to have the Code and Description in a table and additionally use this as apply-map-source.

Thanks in advance.

Chris

Re: Mapping Load from Excel, ApplyMap from Excel-Table

tresB — Mon, 16 Nov 2015 10:39:53 GMT

Mapping tables are automatically dropped after the reload. If you need them in the data model, load the table without Mapping (load) keyword once again.

Re: Mapping Load from Excel, ApplyMap from Excel-Table

rubenmarin — Mon, 16 Nov 2015 10:40:43 GMT

Tables loaded as mapping doesn't not remain as loaded tables, they are automatically deleted at the end of script.

Load the code in the table you're using ApplyMap.

Re: Mapping Load from Excel, ApplyMap from Excel-Table

Tue, 17 Nov 2015 11:17:36 GMT

Hi everybody,

thanks for all the quick and correct suggestions

Chris