topic Re: Re: Qlik Sense & Syntax for Mapping in App Development

Qlik Sense & Syntax for Mapping

jcampbell — Mon, 01 Dec 2014 17:57:03 GMT

I have a Qlik Sense file in which I'm trying to use a script from a QlikView document wherein I perform some field name changes. Here's the script:

FieldMap:

MAPPING

LOAD

oldnames,

newnames

FROM

[..\Lookup Tables\FieldMappings.xlsx]

(ooxml, embedded labels, table is FieldMappings);

RENAME Fields using FieldMap;

This will not work with Qlik Sense. I tried making a change using the data connection syntax, e.g.

FieldMap:

MAPPING

LOAD

oldnames,

newnames

FROM

[lib://Scripts (network_servername)/Lookup Tables/FieldMappings.xlsx]

(ooxml, embedded labels, table is FieldMappings);

RENAME Fields using FieldMap;

That did not work either. Any suggestions? The is the last bit to get my script fully functioning in Qlik Sense.

Re: Qlik Sense & Syntax for Mapping

JonnyPoole — Mon, 01 Dec 2014 18:40:30 GMT

This did work for me using the lib// syntax .

Book2 below is my data file. It has a field called 'A'.

My mapping load is book1 which has a row for A to rename as B

Here is the script using your code for the mapping load

Data:

LOAD

Value

FROM [lib://temp/Book2.xlsx]

(ooxml, embedded labels, table is Sheet1);

FieldMap:

mapping

LOAD

oldnames,

newnames

FROM [lib://temp/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

RENAME Fields using FieldMap;

...you can see that the resulting data model did have field 'A' renamed as 'B'

Re: Re: Qlik Sense & Syntax for Mapping

jcampbell — Mon, 01 Dec 2014 20:41:37 GMT

I added my response to the wrong post earlier. Sorry about that...

Jonathan,

Thanks for your help. That's the syntax / format I'm using. I also tried adding it via the UI by selecting it via the wizard in the 'Data load editor' but that results in an error. Please see the attached screen captures.

The debugger doesn't include a very details error either (also attached). I performed a full reload but it didn't reveal much more in the error message.

Thanks,

Josh

Re: Qlik Sense & Syntax for Mapping

JonnyPoole — Tue, 02 Dec 2014 01:14:19 GMT

I retriedtrying to make every aspect of the mapping load (data connection, file name, sheet name ) etc.. but it still worked for me.

I suppose it could be your mapping load spreadsheet. Do you want to upload it ?

Also, i am using the updated version 1.0.1 released this month in case you are not.

The only other idea is to try your mapping load in a brand new app with a simple example like mine below and work back from there.

-----------------------

Data:

LOAD

Value

FROM [lib://temp/Book2.xlsx]

(ooxml, embedded labels, table is Sheet1);

FieldMap:

mapping

LOAD

oldnames,

newnames

FROM [lib://Scripts (network_servername)/Lookup Tables\FieldMappings.xlsx]

(ooxml, embedded labels, table is FieldMappings);

RENAME Fields using FieldMap;

Re: Re: Qlik Sense & Syntax for Mapping

jcampbell — Tue, 02 Dec 2014 15:04:05 GMT

Jonathan,

Could be. Attached you'll find the Excel spreadsheet I'm using. Let me know if you notice anything wrong with it.

Thanks,

Josh

Re: Re: Qlik Sense & Syntax for Mapping

jcampbell — Tue, 02 Dec 2014 15:15:21 GMT

I was finally able to get this to work. I had to do three things:

1. Change the file to an XLS not XLSX.

2. Invert one of the forward slashes in the directory syntax e.g. change "FROM [lib://Scripts (network_servername)/Lookup Tables/FieldMappings.xlsx]" to "FROM [lib://Scripts (network_servername)/Lookup Tables\FieldMappings.xlsx]".

3. Change "(ooxml, embedded labels, table is FieldMappings);" to "(biff, embedded labels, table is FieldMappings$);"

That did the trick. So the final version looks like this:

FieldMap:

MAPPING

LOAD

oldnames,

newnames

FROM [lib://Scripts (network_servername)/Lookup Tables\FieldMappings.xlsx]

(biff, embedded labels, table is FieldMappings$);

RENAME Fields using FieldMap;