Easy data dictionary from your QVDs

dgreenberg — Mon, 27 Feb 2017 18:01:49 GMT

What happens when you have a bunch of QVD’s that feed your data model and you want to create a data dictionary listing Table, Field and a row or 2 of sample data.

That’s the challenge I just helped a co-worker with.

In my case the QVD’s had qualified field names so the employee and phone fields in the master table would look have a field name of master.employee, master.phone and so on.

If that’s not the case you can add another line to your script QUALIFY *;

It’s very simple and just 1 line of script, 2 if you need to add the QUALIFY *;

Code:

First 2 Load * from MyLocation\*.qvd (qvd);

This will loop through all QVD’s in the specified location and load 2 records from each table. Keep in mind some columns may not have a value in the first or 2^nd row but I didn’t want to go crazy given the large QVDs and was afraid if I tried looking for values it would take too long.

Now create a Straight Table Chart

Dimensions:

$Field

Expressions:

Table

=left($Field,Index($Field,'.')-1)

Field

=right($Field,len($Field)-Index($Field,'.'))

Sample Value 1

=FieldValue($Field,1)

Sample Value 2

=FieldValue($Field,2)

The only other thing you want to do is to go to the presentation tab and for the first column $Field you want to check (*) Hide column. This is something I almost never do as it can have very bad results but in this case the chart must have at least one dimension and at least one expression so this is how I worked around it.

When you run it you end up with something like this with more rows and a 2^nd sample value I omitted for space reasons.

topic Easy data dictionary from your QVDs in QlikView

Easy data dictionary from your QVDs