Load from Multiple SQL Tables with same fields

[gerhardl]

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

[Anil_Babu_Samineni]

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';

[gerhardl]

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.

[Anil_Babu_Samineni]

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.

[jonathandienst · Accepted solution]

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

[gerhardl]

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

[effinty2112]

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

[gerhardl]

This is perfect, thanks.

Just had to change

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

to

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

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

Works like a charm.

[jonathandienst]

Glad to have helped.

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