topic Script syntax in App Development

Script syntax

Tue, 28 Jun 2016 00:23:59 GMT

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

Re: Script syntax

sunny_talwar — Tue, 28 Jun 2016 00:44:19 GMT

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

Re: Script syntax

Tue, 28 Jun 2016 00:51:34 GMT

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.

Re: Script syntax

sunny_talwar — Tue, 28 Jun 2016 13:47:27 GMT

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