Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Mapping Load from Excel, ApplyMap from Excel-Table

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.

9 Replies
balrajahlawat
Esteemed Contributor

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

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

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

MVP
MVP

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

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

Hi Christian,

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

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

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;

Not applicable

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

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

MVP
MVP

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

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

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.

Not applicable

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

Hi everybody,

thanks for all the quick and correct suggestions

Chris

Community Browser