6 Replies Latest reply: Jun 13, 2012 11:40 AM by Alan Hendrickx RSS

SQL help

Alan Hendrickx

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

  • SQL help
    Rob Wunderlich

    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

    • SQL help
      Alan Hendrickx

      Hi Rob,

       

      That's great, thanks very much.

      I really appreciate it.

       

      Alan

    • SQL help
      Alan Hendrickx

      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

      • SQL help
        superquinn

        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

        • SQL help
          Rob Wunderlich

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

           

          -Rob

          • Re: SQL help
            Alan Hendrickx

            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;