Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Script syntax

Hi, I've tried looking at videos and help files but I just can't seem to find the right answer.

I have not attempted this before so I need all of this syntax to be checked for me but in particular I think the only thing that won't work is that I don't know how to make the path correct at the end of this script. I don't know how to enter the sheet name especially I think.

If my Excel file is "source.xlsx" and the relevant sheet within that workbook is called "ESA NAMES" then I am trying to use the following script to convert these codes (such as "4BAY") to names (such as "Bayside"). If someone could please help me so that I can copy and paste your amended script directly into Qlik that would be very much appreciated. Thanks.

 

mapCode:

mapping Load * Inline [

Code , Description

4BAY, Bayside

4CAL, Calder


4GOV, Goulburn


4KIE, Kiewa

 

4MAO, Maroondah

4OVE, Ovens


4PLE, Plenty

 

];

excel:

load *, applymap('mapCode', Code, '#NV') as Description From C:\Users\cellingworth\CHRIS\QLIK\DES TEST\Presentation\source.xlsx\ESA NAMES

 

1 Solution

Accepted Solutions
sunny_talwar

Chris -

Qlik Sense has its own file wizard (may be called a little different) which can be used to load an Excel file. I suggest using the wizard instead of manually typing the syntax out as we are all prone to errors.

Look at this video: Loading Excel and File Data (video)

Best,

Sunny

View solution in original post

3 Replies
sunny_talwar

May be like this:

mapCode:

Mapping

LOAD * Inline [

Code , Description

4BAY, Bayside

4CAL, Calder

4GOV, Goulburn

4KIE, Kiewa

4MAO, Maroondah

4OVE, Ovens

4PLE, Plenty

];

Excel:

LOAD *,

          ApplyMap('mapCode', Code, '#NV') as Description

FROM

(ooxml, embedded labels, table is [ESA NAME]);

If you don't have embedded lables, you might need to make it no labels. Best way would be to use QlikView to create this for you using the File Wizard

Capture.PNG

Not applicable
Author

Hi Sunny. I don't have QlikView is this unusual? I just have Qlik Sense. I get an error message when I try this in the data load editor. Is this because I don't have embedded labels? What else would you suggest?

Thanks.

sunny_talwar

Chris -

Qlik Sense has its own file wizard (may be called a little different) which can be used to load an Excel file. I suggest using the wizard instead of manually typing the syntax out as we are all prone to errors.

Look at this video: Loading Excel and File Data (video)

Best,

Sunny