0 Replies Latest reply: Aug 16, 2017 11:46 AM by Max Ålander RSS

    SQL select by using a list of strings

    Max Ålander

      In the source DataSource there are tables table names of dates, i.e. '2017-05-01', where each table contains a data point I'm after. This is a format which I feel is weird and difficult to work with. Is that true or is my knowledge inadequate?


      Anyway. To extract the data through an SQL query, Qlik suggests using the following script when selecting data and creating an automatic script in the Data Load Editor:

       

      NewTable:

      SQL SELECT,

          "2017-04-08",

          "2017-04-09",

          "2017-04-12",

          "2017-01-05",

          "2017-01-06"

      FROM DataSource;

       

      This query has a set number of dates which represents the time frame I'm extracting data from. If I expand the time frame include more dates I have to manually update the script (or insert an automatically created script which still requires manual work).

       

      Therefore I have rewritten the code to handle an arbitrary amount of dates/tables. Here I loop through dates and insert them into the SQL query one at a time. Within the loop I can then continue working with the data I added to NewTable. (It should also be mentioned that I have a table with all the dates to my disposal.)

       

      Dates:                                                                          

      LOAD [@Value] as "InstallDate"

      RESIDENT DatesTemp;

       

      FOR i = 1 to 5

          LET temp = FieldValue('InstallDate', (i));

       

          NewTable:

          SQL SELECT

                "$(temp)"

          FROM DataSource;

      NEXT i;

       

      This script works fine but I feel that it's not an optimal way of doing it.

       

      Now to my first question:

      Is it possible to, in the SQL query, use a list of dates directly instead of just fetching one value at a time within a loop. Something like:

      NewTable:

      SQL SELECT

          "$(dates)"                         //where dates is a list of dates

      FROM DataSource;

       

      And my second question:

      Is it better to call the SQL query as few times as possible or does my previous solution with the query in a loop work just as fine?

       

      I hope I made this somewhat clear. Please don't hesitate if you have any questions about the setup.

      Thanks!

      Max

       

      Note! The examples I use here are simplified to focus on the problem at hand.