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

    SQL help

    PortoLad

      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
              PortoLad

              Hi Rob,

               

              That's great, thanks very much.

              I really appreciate it.

               

              Alan

              • SQL help
                PortoLad

                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
                            PortoLad

                            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;