topic Using a looped variable in SQL load where clause? in QlikView

Using a looped variable in SQL load where clause?

AyCe1082 — Sat, 16 Nov 2024 00:20:19 GMT

Hi, is it possible when doing an SQL load to add a where clause that has values in an Oracle DB Column not equal to $(variable) ?

The variable will loop through a list of IDs so the resultant table now has rows of data for any new IDs only.

Re: Using a looped variable in SQL load where clause?

Kushal_Chawda — Sun, 12 Jul 2020 21:22:02 GMT

Yes, you can use the variable in SQL statement. Can you please elaborate further? You can do something like below

SQL

Select ID

FROM schema.tablename

where ID not in '$(variable)'

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Sun, 12 Jul 2020 22:28:01 GMT

Hi, I was wondering more about creating the loop itself (and having it compatible with the query). The loop would go through a list of numeric IDs one at a time while the SQL load is happening or if it is possible to have the entire list.

The list could be something like

2213563

2213423

2231343

2245234

2235446

3345336

etc. and during the SQL load the variable would check all the IDs and only load in rows that don't match them. The list could be very large, possibly in the thousands so I am not sure if I should be using a loop or if the entire list can be used.

Re: Using a looped variable in SQL load where clause?

Kushal_Chawda — Sun, 12 Jul 2020 22:39:21 GMT

From where your list will come?

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Mon, 13 Jul 2020 09:31:43 GMT

The list comes from a cmd execution that writes a list of PDF filenames from folders on the server (which contain the IDs in the name) to a text file and then breaks up the filenames to get the ID as its own column.

Then each SQL load will check the IDs from that list and only pull rows with new IDs from the DB, so the size of each query is kept to a minimum and NPrinting isn't generating the same PDFs on every reload.

Re: Using a looped variable in SQL load where clause?

Kushal_Chawda — Mon, 13 Jul 2020 09:57:24 GMT

one option will be read the text file and store the values as concatenated list in variable and then use that variable in SQL where clause

List:

load concat(chr(39)&ID&chr(39),',') as List

FROM textfile;

let vList = peek('List',0,'List');

SQL

Select ID

FROM schema.tablename

where ID not in '$(vList)';

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Mon, 13 Jul 2020 10:31:13 GMT

I got a "string literal too long" error unfortunately.

Re: Using a looped variable in SQL load where clause?

Kushal_Chawda — Mon, 13 Jul 2020 12:43:49 GMT

may be typo.

List:

load concat(chr(39)&ID&chr(39),',') as List

FROM textfile;

let vList = peek('List',0,'List');

SQL

Select ID

FROM schema.tablename

where ID not in ($(vList));

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Thu, 16 Jul 2020 09:37:06 GMT

I use the following code and do get the ID numbers but the list is extremely long because there are I think a few thousand IDs.

PDF: LOAD subfield([@40:n],'_',2) as report_matchdate, subfield([@40:n],'_',3) as report_matchname, subfield([@40:n],'_',4) as report_gameId, subfield([@40:n],'_',5) as report_version, replace(subfield([@40:n],'_',6),'.pdf','') as report_language, if(num([@21:38])<1000000,'repeat','complete') as report_state FROM [...\Match_Report\filelist_ORA1.txt] (fix, codepage is 1252, header is 9 lines) where num([@21:39])>0 and num([@21:39])<10000000 ; PDFList: load concat(chr(39)&report_gameId&chr(39),',') as PDFlist resident PDF; let vList = peek('PDFlist',0,'PDFlist'); Then in the SQL load the line used is AND load.GAME_CODE not in '$(vList)'

Re: Using a looped variable in SQL load where clause?

cwolf — Thu, 16 Jul 2020 17:42:45 GMT

Of course, SQL strings are limited in length. Oracle has a limit of 409600 for SQL strings.

Your list has a lot of duplicate values. Use:

PDFList:
load concat(distinct chr(39) & report_gameId & chr(39),',') as PDFlist
resident PDF where len(report_gameId)>0;

In the sql statement the list must be enclosed by parentheses:

where ... GAME_CODE not in ('$(vList)');

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Fri, 17 Jul 2020 11:23:00 GMT

I don't think the string method will work, the string is just too long, even with distinct IDs.

The scroll bar in the image shows just how long the ID list is (and the list will just keep growing anyway).

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Sun, 19 Jul 2020 20:11:27 GMT

I have tried a different method and am close to the end but I have to get this last step right.

I have now two qvds,

The first qvd named ReportsTracker(which is created from the text file) has the unique IDs, a key I made, and a status column that is 0 or 1. It's a 1 if a filename exists in the server (so there is a PDF).

The second qvd named ReportsMain has all the data I pulled from the DB including IDs plus the key and status column manually added in after. The status column is always 0 here.

The goal is to change the values in the status column of ReportsMain from 0 to 1, if it happens to be a 1 in the ReportsTracker qvd. I know I am currently doing it wrong in the code below, as the final table is just what was in the ReportsTracker qvd. I'm wondering how to finish this and have it working? If applymap is the way to go or some type of join?

Main: LOAD * FROM [D:\QlikView\DEV\DBQVD\Soccer\ReportsTracker.qvd] (qvd); Concatenate(Main) LOAD * FROM [D:\QlikView\DEV\DBQVD\Soccer\ReportsMain.qvd] (qvd) where not exists(Reportkey);

Re: Using a looped variable in SQL load where clause?

AyCe1082 — Mon, 20 Jul 2020 10:30:42 GMT

Got it working with mapping load. All is good now. I don't know how to lock or delete a topic.