Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Re: Script syntax

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

3 Replies
MVP
MVP

Re: Script syntax

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

Re: Script syntax

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.

MVP
MVP

Re: Script syntax

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