Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
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
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
Hi Rob,
That's great, thanks very much.
I really appreciate it.
Alan
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
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
Matt's response is the best solution I know of.
-Rob
Hi folks,
Many thanks for the above responses - it's extremely helpful.
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).
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
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;