Qlik Sense & Syntax for Mapping

[jcampbell]

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.

[JonnyPoole]

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

    A,

    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'

[jcampbell]

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

[JonnyPoole]

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

    A,

    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;

[jcampbell]

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

[jcampbell · Accepted solution]

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;