topic Re: Qlikview tables metadata in QlikView

Qlikview tables metadata

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

Hi All,

I need to compare metadata for last 2 months data.

2018-Jan: Tables	2018-Feb: Tables
2018_1_Table1	2018_2_Table1
2018_1_Table2	2018_2_Table2
2018_1_Table3	2018_2_Table3
2018_1_Table4	2018_2_Table4
2018_1_Table5	2018_2_Table5

By picking all the required columns i'm creating a single table using concatenate as below.

Final Table: Columns
Date (Derived from table name)
Table Name (Derived from table name)
Other Columns (required columns from all the tables)

Now I need to create a new metadata table with information like below.

MetaData of all 2 months tables
Date (Month Name of Table)
Table Name
No of rows
FieldTotalCount
FieldDistinctCount
FieldUniqueCount

Can anyone help me creating this final metadata table?

Re: Qlikview tables metadata

qlikbobby — Wed, 22 Aug 2018 19:39:35 GMT

any thoughts on this?

Re: Qlikview tables metadata

eliran — Wed, 22 Aug 2018 20:05:49 GMT

Hi,

I'll suggest a different approach, given all your data is stored in QVD's, you can load the metadata itself from the QVD's.

The method is loading it into the script editor with the wizard, and change the loading from QVD to XML.

When doing so, you're getting a lot of meta data, what's the app that created this qvd, how many rows it got, unique values on each field and so on..

That way, you can take the relevant meta data from each file, and analyze it as you wanted.

Please see attached, I hope it helps.

Eliran.

Re: Qlikview tables metadata

qlikbobby — Wed, 22 Aug 2018 20:20:32 GMT

Hi Eliran,

Thanks for your quick reply. I'm wondering which columns represent unique values and distinct values in your approach?

Re: Qlikview tables metadata

eliran — Wed, 22 Aug 2018 20:37:29 GMT

Hi Sasi,

There is a field called NoOfSymbols that represents the number of unique values in each field.

In the above example, its a file holding 830 rows, the ID is the PK so each id is different.

We can see there are 89 different customers, 9 employees, etc..

Eliran.

Re: Qlikview tables metadata

qlikbobby — Fri, 24 Aug 2018 14:23:43 GMT

Hi Eliran,

It is working perfect but is there any way that we can also keep track of NULL values?

Re: Qlikview tables metadata

eliran — Tue, 28 Aug 2018 09:38:28 GMT

LHi Sasi,

I don't think you can extract this information from the metadata part.

You will need to perform a load script, counting only if value is null for each of the fields, and then join the results with the above metadata, see example:

LOAD 'TableName' as TableName,

count(if(isnull(OrderID),1)) as OrderID,

count(if(isnull(CustomerID),1)) as CustomerID,

etc..

FROM

(qvd);

Temp2:

CrossTable(FieldName,Nulls)

load * Resident Temp;

drop Table Temp;

The result of this will be a table with 3 columns holding the table name, field name, and the number of nulls.

Then just do with it as you please.

Regards,

Eliran.