Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Steve,
Just want to add, the end-users always got their "user friendly" field names, I just moved aliasing to the last step before releasing a user-interface. All ETL layers prior used source DB nomenclature.
Regarding using functions to generate syntax...
Completely with you on using the Excel functions to form scripting syntax for aliasing. I may have some additional ideas on how this can be expanded upon, and also brought back into being performed within QlikView.
I've done a decent amount of exploration for creating programmatic scripting within QlikView, not just for forming QVS syntax, but also with SQL, XML, and DOS. The results have allowed pushbutton generation of complete code routines for all of these languages (and I'm sure more are possible).
For example in our aforementioned scenario, by attaching the data catalog to auto-scripting routines it was possible to create QVS syntax that wrapped the field names from both the QV and native RDMS side (load and preceding) in data typing functions, correlating to their catalog data type definitions.
(why not allow Qlik to intuit the data types and skip explicit data typing functions altogether? Because sometimes in the case of Dual() type fields, it was necessary to control which side of the dual was the pertinent half. Other cases, naked field references were distorting due to potential numeric vs. textual interpretations. i.e. Is '82E2' a text handle for a company division? Or a numeric representation in exponential format? Have you ever had a combined QV field receiving contributions from multiple tables load numeric from the first source, and then get confused because later tables introduce text? vs. versa? Explicit data type functions were closing down a lot of these avenues for data 'misinterpretation')
But if you look at the screenshot, you can see the fully-formed QVS scripts being prepared in the Text object. It takes into account a lot of regularly repeated steps beyond just identify the table name, source, and fields, by also making syntax statements for script tabs, connection strings, comments, STORE, DROP, and DISCONNECT.
Taking this one step further, I was using a macro to export the formed scripts into brand new .QVW documents. Upon testing the push-button generated ETL .QVW, they were running freshly made without issue and now the creation of all ETL QV scripting within that development environment was completely standardized. And setting the stage for standardization across all projects/environments.
Then lets say the underlying RDMS table structures began to change. Fields dropping in and out, data types shifting, case-sensitive names jumping up and down. To adapt to this, the data catalog was reloaded (even on a schedule), then a project definition identifing the target group of tables was selected, push the 'autogenerate' button again and a modified ETL .QVW up to date with the most current DB definitions was produced, and it did not use wildcard or naked field references in any way.
It was also modified to handle extracting the same table across multiple environments/sources, so if you needed to pull COMPANY_CODES from a DB instance in DEV, QA, and PROD and also from one ORACLE farm and one SQL Server farm, it was able to script an extraction for the same table 6x and make folders/QVD instances that labelled each according to its source so that they wouldn't get confused by being commingled into one giant QVD folder.
Every presentation of this system to clients and Qlik partners has been greeted with lots of enthusiasm, but then during the afterthought, it tends to get buried deeper than Randy Hearst's release of Orson Wells's 'Citizen Kane' 
, so I'm not sure when or how an idea of this nature could take form in the general consensus. But for my personal usage, it works great!
Steve, I appreciate you taking the time to even read these posts, we kind of got away from the main topic of the thread but being able to share this concept means a lot.
There's one other portion of preserving the underlying structure of the source systems I wanted to mention, it deals with preserving the data "address" information in the folder structure of the extract QVD repository.
When I use this application, it was important to avoid having large amounts of QVDs pile up in a giant folder (and also table names repeat). So the application checks to see if the user is creating an extraction for a new dsn/schema combination from the data catalog. If the combination of dsn/schema's haven't been extracted prior, the application goes out and creates that folder tree in preparation for the landing zone of the QVD output of the newly generated extraction script.
The EXTRACT folder structure was standardized and always followed a specific syntax that mirrored the structure of the RDMS: \{DSN}\{Shema}\{Table}.QVD
That way the load statement for a QVD retains a near identical "address" syntax that the RDMS system uses. Even if I weren't familiar with QlikView, but familiar with databases I should be able to follow the nature of where a QVD is sourcing from, and keeping things in a kind of "apples to apples" comparison.
i.e.
ODBC Connect to MSSQLSRV_QA;
SQL SELECT * FROM [edw].[PRODUCTS];
//has the same navigation attributes as:
LOAD * FROM
[.\MSSQLSRV_QA\edw\PRODUCTS.QVD (qvd);]
It seems to me this system is generic enough to be compatible with any QlikView installation using ETL layers in conjunction with DSN sources, which has been every project I've ever been on. Maybe we could take this offline and discuss some ideas about presenting this as a proposal like you mentioned, I'd be interested in hearing your thoughts about that. And again, thank you for evaluating the idea. (and thank you manojqlik, sorry about this extra discussion in your thread).