topic Re: Select the db based on the column date in QlikView

Select the db based on the column date

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

Hi Masters,

I have in a server many data bases such as:

DW201501

DW201502

DW201503

DW201601

DW201701

DW201702

DW201703

DW201704

DW201705

It is always 'DW+'Year'+'Month'

After loading a table, I would like to do a join with the correct DB based on the year of a column date . Meaning, if the date is

07-March-2015, upon doing the join, the query should search for the DW201503 or any DW2015XX.

Is it possible?

Please advise

Thanks!

Re: Select the db based on the column date

Peter_Cammaert — Tue, 17 Oct 2017 15:42:28 GMT

If these databases all have the same layout, then you can load & concatenate them all into the same resident table in QlikView. By adding columns that contain the YearMonth value lifted from the database name, you can create a key field that includes these YearMonth values and link to other tables in the usual way.

If these database contain a date field with dates that are covered by the period as indicated in the database name, then distilling the period from the database name may not even be needed. But to go any further, you need to explain what data is in these databases and to what content you want to connect them.

Re: Select the db based on the column date

effinty2112 — Tue, 17 Oct 2017 15:56:03 GMT

Hi Felipe,

I have a similar situation to you and, as Peter has suggested to you, I concatenate the tables. From each of my databases I extract three large tables. To make it easy I suggest writing a subroutine with the database name as an argument. I have three subroutines and run each of them once for each database to produce my three resident tables.

By concatenating you may not need to do the kind of joins you describe.

Regards

Andrew

Re: Select the db based on the column date

felipe_oliveira — Tue, 17 Oct 2017 17:32:26 GMT

Thanks a lot! would you have short sample of script that should use to extract, for example, table XXXX from the server and concatenate them?

Re: Select the db based on the column date

felipe_oliveira — Tue, 17 Oct 2017 17:33:00 GMT

Thanks a lot! would you have a short sample of script that I should use to extract, for example, table XXXX from the server and concatenate them?

Re: Select the db based on the column date

effinty2112 — Wed, 18 Oct 2017 07:32:34 GMT

Hi Felipe,

Maybe a subroutine like:

Sub LoadTable(DB,SourceTableName)

TRACE;

TRACE FROM $(DB);

TRACE;

'$(SourceTableName)':

LOAD

FIELD1 AS FieldName1,

FIELD2 AS FieldName2,

'$(DB)' AS Source

;

SQL SELECT

FIELD1,

FIELD2,

FROM $(DB).SYSADM.$(SourceTableName); *you may need to amend this line to suit your system

End Sub;

You can add more arguments if you like. Maybe an argument to give the resident table a name different from the source name or parameters you can put in a WHERE clause.

To concatenate just call the subroutine with different arguments

Call LoadTable('DW201501','XXXX');

Call LoadTable('DW201502','XXXX');

Good luck

Andrew