topic Using multiple scripts in QlikView

Using multiple scripts

kevincagle — Wed, 25 Nov 2020 16:16:04 GMT

Hello all, My script is getting quite long in the script editor, MAIN tab.

I tried to make new tabs to apply various scripts for ease of editing and organization, however I cant figure out how to make Qlikview read from the other tabs.

Example:

State:
Mapping Load * Inline [
StateLong, State
Alabama, AL

This list is getting long, only around 60 with states and US territories. However if I use city, country instead of state this list grows to hundreds. I need to start working on organization of the content before I get to our services list, which is thousands of mapping entries

How do I make QlikView read from multiple tabs of the script editor when loading data?

Re: Using multiple scripts

Gysbert_Wassenaar — Tue, 30 Oct 2018 17:46:52 GMT

You could put your list in a text or excel file and load from that. Or create multiple inline load statements that concatenate the data to the same table. Then finally create the mapping table using a resident load:

States:

LOAD * INLINE [

StateLong, State

Alabama, AL

];

// on a next script tab

CONCATENATE (States)

LOAD * INLINE [

StateLong, State

Florida, FL

];

...etc

// on yet another script tab

mapStates:

MAPPING LOAD * RESIDENT States;

DROP TABLE States;

Re: Using multiple scripts

kevincagle — Tue, 30 Oct 2018 18:00:11 GMT

The data is already on spread sheets, issue is each tab of the spread sheet is pulling from different systems. Some systems abbreviate the state, country, day, and month where others use the full spelling. So instead of manually changing many cells on 50 spread sheet tabs every time the reports are pulled, its easier and more efficient to have QV map to it through Mapping (which I just learned how to do 3 hours ago).

Re: Using multiple scripts

Gysbert_Wassenaar — Tue, 30 Oct 2018 18:01:58 GMT

Ok, fine, but nothing you just said explains to me why you can't also put the data for the mapping table in a spreadsheet as well.

Re: Using multiple scripts

kevincagle — Tue, 30 Oct 2018 18:13:59 GMT

Im not sure I follow what you are saying? If its what Im thinking, you mean to put the states with abbreviations into a different spreadsheet and call that one up. Not sure how to do that and make it work the same way. Not only that it would require the spreadsheet to accompany the document or have it in a set location. I want to avoid having additional files if the QV file can have it 'preloaded', if that makes any sense.

Im fairly new to qlikview.

What I do now is :

State:
Mapping Load * Inline [
StateLong, State
Alabama, AL
Alaska, AK
Arizona, AZ
Arkansas, AR
California, CA
Colorado, CO

];

Then I apply the map in the relevant sections.

LOAD ApplyMap('State', State) as State,

Im looking at your first reply and trying to break it down so I can see how it works and apply it. If I have any questions Ill reply here. Thank you,

Re: Using multiple scripts

kevincagle — Tue, 30 Oct 2018 19:19:38 GMT

Ok, on main tab I have

MAPPING LOAD * RESIDENT States;

DROP TABLE States;

On tab 2 I have

States:
Load * Inline [
StateLong, State
Alabama, AL

];

On tab 3 I have

CONCATENATE (States)

LOAD * INLINE [
StateLong, State
];

Im getting errors when trying to reload the script. Says table not found, 'States' not found. Mapping Load * Resident States

Table Drop Tables statement not found.

Clearly Im misunderstanding something. lol

Then I realise, that applies if I created an independent file as you were suggesting.

Re: Using multiple scripts

Gysbert_Wassenaar — Wed, 31 Oct 2018 11:14:02 GMT

Since you mention loading data from spreadsheets I assume you know how to load data from excel files. If you create an excel file with two columns StateLong and State and fill that with the data you want to use for the mapping then you should be able to load those two columns from the excel file.

State:

MAPPING LOAD StateLong, State FROM (ooxml, embedded labels, table is Sheet1);

Re: Using multiple scripts

Gysbert_Wassenaar — Wed, 31 Oct 2018 11:15:50 GMT

A resident load will load data from a table that was created earlier in the script. That's why you get the error. The table you're trying to load data from doesn't exist yet. Put the mapping load statement after the statements that create and concatenate data to the States table.

Re: Using multiple scripts

kevincagle — Wed, 31 Oct 2018 14:39:34 GMT

Can you answer me this?

I have 2 tabs in script editor

Tab 1 = Main

Tab 2 = State List

On Main, its the standard load info.

HeadCount:
LOAD ApplyMap('State', State) as State,
City,
Headcount

FROM
[.\mypath\Data.xlsx]
(ooxml, embedded labels, table is Headcount);

On State List I have the following.

State:
Mapping Load * Inline [
StateLong, State
Alabama, AL ] ;

Why does it say Table not found (State) when I reload?

If you answered it above, Im not understanding it.

Re: Using multiple scripts

sudeepkm — Wed, 31 Oct 2018 14:45:11 GMT

I think you need to put the table first then load it in your script.

This should be first

State:
Mapping Load * Inline [
StateLong, State
Alabama, AL ] ;

Then the mapping load that refers it.

Re: Using multiple scripts

kevincagle — Wed, 31 Oct 2018 15:32:39 GMT

Ah, I see now. The tabs can be promoted/demoted. I didnt notice that before.

Thank you!