topic Re: Load from Multiple SQL Tables with same fields in QlikView

Load from Multiple SQL Tables with same fields

gerhardl — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

On our SQL Server there is a table with transactions for each month. A new table gets created on the 1st day of every month.

So tables will look identical and will be named as follows:

dbo.F_Transaction_201607

dbo.F_Transaction_201608

dbo.F_Transaction_201609

dbo.F_Transaction_201610

and so on.

I currently load data from the latest table (F_Transaction_201610) but am looking for a way to repeat the load for each table greater than August 2016. So QlikView will need to recognize the date in the table name, and then basically load the same fields from all tables on the DB where the YYYYMM >= 201608

Re: Load from Multiple SQL Tables with same fields

Anil_Babu_Samineni — Tue, 04 Oct 2016 09:06:36 GMT

You can use this using Concatenate or try Simple Join

Load * FROM

dbo.F_Transaction_201607 where YYYYMM >= '201608';

Concatenate

Load * FROM

dbo.F_Transaction_201608 where YYYYMM >= '201608';

Concatenate

Load * FROM

dbo.F_Transaction_201609 where YYYYMM >= '201608';

Concatenate

Load * FROM

dbo.F_Transaction_201610 where YYYYMM >= '201608';

Re: Load from Multiple SQL Tables with same fields

gerhardl — Tue, 04 Oct 2016 09:26:53 GMT

Sorry, that won't work. It would mean creating a separate piece of load script every month, for every table.

The whole point is that I only want to have 1 piece of SQL load, with a variable FROM location.

Re: Load from Multiple SQL Tables with same fields

Anil_Babu_Samineni — Tue, 04 Oct 2016 09:35:34 GMT

I've create Year base, from 2013 to Till

I have data each year has YYYYMM == 201404 like this

For me it's working. Why it's not working for you. I've created recently which is 1/2 weeks back.

Would you able to provide what you want to achieve.

Re: Load from Multiple SQL Tables with same fields

jonathandienst — Tue, 04 Oct 2016 09:44:03 GMT

Like this, perhaps

// Fetch matching tables (SQL Server specific code)

T_Tables:

SQL SELECT DISTINCT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

TABLE_NAME Like 'F_Transaction%' AND TABLE_SCHEMA = 'dbo'

;

// Create target table

Data:

LOAD 0 as Dummy AUTOGENERATE 0;

// Loop over tables and laod those where the month > 201608

For i = 0 To NoOfRows('T_Tables')

Let vTable = Peek('TABLE_NAME', i, 'T_Tables');

Let vQTable = 'dbo.[$(vTable)]';

Let vMonth = Num(Right(vTable, 6));

If vMonth >= 201608 Then

Concatenate(Data)

SQL SELECT * FROM $(vQTable);

End If

Next

// Cleanup

DROP Table T_Tables;

DROP Field Dummy From Data;

Edit: fixed 2 syntax errors

Re: Load from Multiple SQL Tables with same fields

gerhardl — Tue, 04 Oct 2016 09:44:53 GMT

It will work, but it is not what I am trying to achieve. You have a separate load "block" for each month:

Load Block 1

Load * FROM

dbo.F_Transaction_201607 where YYYYMM >= '201608';

Load Block 2

Concatenate

Load * FROM

dbo.F_Transaction_201608 where YYYYMM >= '201608';

Load Block 3

Concatenate

Load * FROM

dbo.F_Transaction_201609 where YYYYMM >= '201608';

Load Block 4

Concatenate

Load * FROM

dbo.F_Transaction_201610 where YYYYMM >= '201608';

Next month I will have to go and add an additional load block to get the November data:

Load Block 5

Concatenate

Load * FROM

dbo.F_Transaction_201611 where YYYYMM >= '201608';

And I'll have to do this every month. There is also no reason to add the "where YYYYMM >= '201608' " to that because each table anyway only has transactions for that month. So I want to load ALL records from these tables, but only records from TABLES where the date in the TABLE NAME (e.g. F_Transaction_201611) is 201608 and greater, because we have tables dating back to 2011 and I do not want to go through all of them.

Also, my load script is long (over 100 lines) because I apply complex logic and mapping, so I would have to repeat that each month and will end up with a load script that is 10,000 lines long.

What I want is this:

Load * FROM

dbo.F_Transaction_$(VARIABLE)

where the variable is a date in YYYYMM format, greater than 201608.

Here is the exact equivalent of what I want, but this loads FILES from a folder on the PC, as opposed to from SQL tables:

// Variable for counting the number of files loaded:

LET FileCount = 0;

// Variable to set the minimun file date:

LET MinFileDate = Date(Today());

FOR Each File in FileList ('Folder\*.txt');

LET FileDate = Date(Date#(Left(Right( '$(File)' ,42),11), 'DD-MMM-YYYY'));

IF $(FileDate)>$(MinFileDate) THEN

LET FileCount = $(FileCount)+1;

TRACE Loading $(File) ($(FileDate));

MyTable:

LOAD * From $(File);

ENDIF

NEXT File;

TRACE No more Files. $(FileCount) files loaded;

In this example, all the .txt are located in a folder called 'Folder', in the same root as the .qvw

Re: Load from Multiple SQL Tables with same fields

effinty2112 — Tue, 04 Oct 2016 09:47:24 GMT

Hi Gerhard,

You could make a temporary table by loading your table names from the TABLES table on SQL Server. Load those table names that match dbo.F_Transaction_YYYYMM name style, use Subfield(FieldName,'_',2) to pick out the date part and you can pick out the tables you need.

Then you can create a loop in your load script and concatenate data from each table.

Cheers

Andrew

Re: Load from Multiple SQL Tables with same fields

gerhardl — Tue, 04 Oct 2016 10:03:19 GMT

This is perfect, thanks.

Just had to change

Let vQTable = 'dbo.[(vTable)]';

Let vQTable = 'dbo.[$(vTable)]';

and you're missing a bracket in the line below that.

Works like a charm.

Re: Load from Multiple SQL Tables with same fields

jonathandienst — Tue, 04 Oct 2016 13:43:54 GMT

Glad to have helped.

That's what happens when you type code in Notepad++ and don't test it.