Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SQL select by using a list of strings

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.

0 Replies