Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell
Partner - Creator
Partner - Creator

Qlik Sense & Syntax for Mapping

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.

1 Solution

Accepted Solutions
jcampbell
Partner - Creator
Partner - Creator
Author

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;

View solution in original post

5 Replies
JonnyPoole
Employee
Employee

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

Capture.PNG.png

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'

Capture.PNG.png

jcampbell
Partner - Creator
Partner - Creator
Author

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
Employee
Employee

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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;