Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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?
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?
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