10 Replies Latest reply: May 22, 2012 5:25 AM by Przemysław Wojda RSS

    How to refer to a variable?

    Przemysław Wojda

      Hi All,

       

      I'm looking for a way to refer to a DB_Name in the below WHERE condition (the one below is, obviously, not working). For the data model purpose I'd like to keep the condition within this bit, not load it into another table.

       

      DBs:
      SELECT DB_Name FROM Configuration;
      
      Data:
      LET NoOfDBs = NoOfRows('DBs');
      FOR i = 0 to $(NoOfDBs)-1
      LET DB_Name = Peek('DB_Name', $(i), 'DBs');
      SELECT
                '$(DB_Name)' AS DB_Name, 
           Dim_1
      FROM $(DB_Name).Table_1 WHERE $(DB_Name) NOT IN ('DB_1');
       
      NEXT
      

       

      Regards,

      Przemek

        • Re: How to refer to a variable?
          Miguel Angel Baeyens

          Hi Przemek,

           

          Is the field "DB_Name" spelled correctly or does it come from the database in table "DBs" all uppercase? Note that QlikView is case sensitive also for the names of the fields, so although the name may be uppercase in the SQL statement, it may be returned in lowercase (for example).

           

          Apart from that, set a TRACE after DB_Name just to check its value:

           

          LET DB_Name = Peek('DB_Name', $(i), 'DBs');
          TRACE $(DB_Name);
          


          Hope that helps.

           

          Miguel

            • How to refer to a variable?
              Przemysław Wojda

              Thank you, Miguel. Correct me if I'm wrong but TRACE $(DB_Name) will only let me to refer to the value and I need to refer to the column name $(DB_Name) to exclude given values. With TRACE $(DB_Name) I get: ABC NOT IN ('ABC') but I need to get DB_Name NOT IN ('ABC').

               

              PS Don't bother upper/lower cases - I've carelessly translated it into English, script is ok.

               

              Regards,

              Przemek

                • How to refer to a variable?
                  Miguel Angel Baeyens

                  Hi Przemek,

                   

                  TRACE only prints in the output and log files the result of the expansion of the variable. If it appears empty, it means it has been unable to retrieve the database name and populate the variable properly. So what is printing the TRACE line when you reload?

                   

                  Miguel

                    • How to refer to a variable?
                      Przemysław Wojda

                      Hi Miguel,

                       

                      TRACE prints the name of my database that is being queried and that is correct. The thing is, however, that from the databases table - DBs - that will be looped I need to exclude one table, and to do it I need to refer to a column name not the value.

                       

                      Przemek

                        • How to refer to a variable?
                          Miguel Angel Baeyens

                          Przemek,

                           

                          So if I understand it right, you are getting the expected value (table name) although you want to exclude some values from there. If my assumption is correct, do a WHERE in the SELECT DB_Name line to exclude as many table names as you want, so the FOR loop only take valid values.

                           

                          Hope that helps.

                           

                          Miguel

                            • How to refer to a variable?
                              Przemysław Wojda

                              To clarify: I first create a table of databases to query from and then do a loop on this databases to get Dim_1 values out of Table_1 in each databes. However, when doing the loop I don't want one table from the DBs to be queried in the loop.

                               

                              DBs: table of databases to be queried, total number - 5

                              Data: table of Dim_1 values from Table_1 from 4 databases in DBs, one database is to be excluded.

                               

                              Hopefully, now it'll be clear :-)

                               

                              Przemek

                    • Re: How to refer to a variable?
                      Deepak Vadithala

                      Hi Przemek,

                       

                      Adding to what Miguel suggested...

                       

                      Are you using SQL Server as your database engine? If yes, then you need to use following naming convention :

                       

                      DatabaseName.SchemaName.TableName

                       

                      or

                       

                      DatabaseName..TableName

                      -- Here the two dots (..) represent default schema for the login

                       

                      So you need use something like this..

                       

                      _________________________________________________________________

                      DBs:

                      SELECT DB_Name FROM Configuration;

                       

                      Data:

                      LET NoOfDBs = NoOfRows('DBs');

                      FOR i = 0 to $(NoOfDBs)-1

                      LET DB_Name = Peek('DB_Name', $(i), 'DBs');

                      SELECT

                                '$(DB_Name)' AS DB_Name,

                           Dim_1

                      FROM $(DB_Name).dbo.Table_1 WHERE $(DB_Name) NOT IN ('DB_1');

                       

                      NEXT

                      _________________________________________________________________

                       

                      Hope this helps!

                       

                      Cheers,

                      DV

                      • Re: How to refer to a variable?
                        jonathan dienst

                        Hi

                         

                        The problem here is that the WHERE clause in the SELECT statement is not valid. This where clause is being passed to the database, and WHERE referencing a <tablename> is not syntax that the database server will understand. Where clauses filter on fields, not tables.

                         

                        If you want to selectively open table, you will need to wrap the SELECT statement with a Qlikview If statement.

                         

                        If '$(DB_Name)' <> 'DB_1' Then

                         

                             SQL SELECT .....

                         

                        End If

                         

                        Hope that helps

                        Jonathan