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

SQL help

Hi All,

I am newish to Qlikview and up to now have just been using Excel as the main data source.

I am new working with a couple of databases - SQL DB and Access DB. Connecting and bringing in data using SQL is ok, however I am not able to rename the fields like I can with an excel field as it's SQL.

Is there an easy way to use the SQL script to being the data in and then transfer this into a regular table that I can work with?

Hope this makes sense.

Thanks,

Al

PS - Any other SQL tips would be appreciated....

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Use a preceeding load to apply qlikview functions to the SQL output, including renames. For example:

mytab:

LOAD ABC as "Cost Basis",

     DEF as "Realized Gain"

;

SQL SELECT ABC, DEF FROM mydb.mytable

;

SQL also provides an "AS" clause you can use to rename, but it's more limited than using the preceeding load.

-Rob

View solution in original post

6 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Use a preceeding load to apply qlikview functions to the SQL output, including renames. For example:

mytab:

LOAD ABC as "Cost Basis",

     DEF as "Realized Gain"

;

SQL SELECT ABC, DEF FROM mydb.mytable

;

SQL also provides an "AS" clause you can use to rename, but it's more limited than using the preceeding load.

-Rob

Not applicable
Author

Hi Rob,

That's great, thanks very much.

I really appreciate it.

Alan

Not applicable
Author

Hi Rob,

Can I ask you another question please?

The above approach requires that I name each field that I want to bring in. The alternative is to load all (Load *;).

Is it possible to combine both approaches i.e. name the fields that I want to name and bring in the remainder as they are?

Thanks,

Alan

Not applicable
Author

Alan,  this should work

LOAD *, ABC AS XYZ;

SQL SELECT......;

DROP FIELD ABC;

There might be a slicker way of doing this, but if you do the above you'll effectively load ABC twice, once under an alias, and then drop the ABC field that arrived in the *. If that makes sense. Using DROP FIELDS you'll be able to drop any other duplicate fields that you have renamed. Although I always find it better to name each field you are loading in the script as you can track what's going on ... bit more time consuming to begin with but can save you plenty of tim ein the long run. - Matt

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Matt's response is the best solution I know of.

-Rob

Not applicable
Author

Hi folks,

Many thanks for the above responses - it's extremely helpful.

Situation

I have run into another issue which I hope that you can help me with. As you are aware I am loading data from an SQL database. In order to make some of the information useful I have had to create a number of mapping tables which I then use as the data is loaded.

The mapping table in the below example takes a numeric list and applies a two digit code instead (which I want to seperate using the Left & Right function - detailed below).

Problem

When I try to use the 'Left' or 'Right' function on the mapped field, 'Field3' in the below example, I get an error when I run the script. The error message tells me that Field3 is not available on database.table2.

I am attempting this directly below the Applymap section, so I would have thought that this would be loaded and available.

Any help would be much appreciated!!

Thanks,

Alan

Example

Tab 1
Mapping_Table1:
MAPPING LOAD
                     Original,
                     MappedTo;

LOAD
                     Original,
                     MappedTo;

SQL SELECT  *
FROM database.table1

Tab 2
Table1:
LOAD
                     Field1,
                     Applymap('Mapping_Table1',Field2) as Field3
                     ;

SQL SELECT *
FROM database.table2;