I am looking for a tool, or a way to build a tool, that can parse through a qlikview script, and output a table with each dimension's initial name and the way it was renamed.
For example; if the below was my input qlikview script:
TableName:
LOAD
Field_1,
Field_2 as [Renamed Field 2]
Field_3
From
[<FILEPATH>.qvd]
;
I would want the output:
Input Name
Output Name
Field_1
Field_1
Field_2
[Renamed Field 2]
Field_3
Field_3
My questions about this are:
Is there an existing tool?
Is there a platform that would be well suited to build this?
Is this information stored anywhere in the documents?
Is there a subroutine or a method that could do this?
This is something I have wanted for a long time. I believe there is nothing native in QV that can provide this information. You could write code to parse the logs, but I think that would be a significant undertaking. I know of a couple of vendors that are looking to create such a feature, but I don't know the status. I have also discussed this requirement with folks at Qlik. Bottom line, nothing I know of exists today to reverse engineer this type of lineage.
"Forward engineered" solutions are possible, and are used by some of my customers. The simplest implementation requires you adhere to a specific script pattern.
1. Create a rename mapping table -- excel sheets or database table(s) -- that define the original fieldnames and the new names.
2. Load the rename table as a mapping table and apply to your data model with script "RENAME FIELDS USING mapname".
3. Avoid using "AS" or "RENAME FIELD xxx" in the script. Derived fields can be given a temporary name in the script and renamed to a permanent name in the map. For example:
(UnitPrice*Quantity) * (1-Discount) AS Derived.TotalPrice
Then "Derived.TotalPrice" is renamed to "Line Total" in the map.
Using this approach, the mapping file becomes the data dictionary. You can include other data -- like source table -- in the mapping file to make this very rich. This approach does require that script follow the standard. It's easy enough to audit QVD and QVW fields against the mapping dictionary and call out any fields that may have circumvented the standard.
A final thought. Some customers who find they need a higher degree of auditabily and control over data opt to build out a full Data Warehouse (DW) using a more sophisticated product that has inbuilt lineage tracking. And then minimize the QV script to simply "SELECT *" from DW tables.
This is something I have wanted for a long time. I believe there is nothing native in QV that can provide this information. You could write code to parse the logs, but I think that would be a significant undertaking. I know of a couple of vendors that are looking to create such a feature, but I don't know the status. I have also discussed this requirement with folks at Qlik. Bottom line, nothing I know of exists today to reverse engineer this type of lineage.
"Forward engineered" solutions are possible, and are used by some of my customers. The simplest implementation requires you adhere to a specific script pattern.
1. Create a rename mapping table -- excel sheets or database table(s) -- that define the original fieldnames and the new names.
2. Load the rename table as a mapping table and apply to your data model with script "RENAME FIELDS USING mapname".
3. Avoid using "AS" or "RENAME FIELD xxx" in the script. Derived fields can be given a temporary name in the script and renamed to a permanent name in the map. For example:
(UnitPrice*Quantity) * (1-Discount) AS Derived.TotalPrice
Then "Derived.TotalPrice" is renamed to "Line Total" in the map.
Using this approach, the mapping file becomes the data dictionary. You can include other data -- like source table -- in the mapping file to make this very rich. This approach does require that script follow the standard. It's easy enough to audit QVD and QVW fields against the mapping dictionary and call out any fields that may have circumvented the standard.
A final thought. Some customers who find they need a higher degree of auditabily and control over data opt to build out a full Data Warehouse (DW) using a more sophisticated product that has inbuilt lineage tracking. And then minimize the QV script to simply "SELECT *" from DW tables.
