topic Re: Best way to Validate QVD's in QlikView

Best way to Validate QVD's

Anonymous — Fri, 22 Jun 2018 05:07:44 GMT

Hi Team,

We are loading data into QlikMart( i mean QVD's) from DB and Flat Files. I am looking for a best approach to validate these QVD's against Db & Flat Files..

Re: Best way to Validate QVD's

kaushiknsolanki — Fri, 22 Jun 2018 05:42:46 GMT

You can create an application which gets record count from all this sources and compare them and get gap analysis.

Regards,

Kaushik Solanki

Re: Best way to Validate QVD's

Anonymous — Fri, 22 Jun 2018 05:55:03 GMT

Not Just about Counts , I am looking to compare detailed comparison with source data

Re: Best way to Validate QVD's

Miguel_Angel_Baeyens — Fri, 22 Jun 2018 06:44:00 GMT

You will need to specify how you actually want to compare the data stored in your data source and the one stored into your QVD.

Consider the following:

Is it just a LOAD * and STORE?

Are there any transformations before storing into QVD?

Do you want to check formats (dates, numbers, length of fields, etc.) or rather amounts (sum for a given period, sum for a given customer, etc.)?

Re: Best way to Validate QVD's

Anonymous — Fri, 22 Jun 2018 06:54:52 GMT

HI,

Only 2 QVD 's are Direct Load. Remaining are having Transformations before the load.

Let me be more clear about requirement Validations:

1. Counts & Sum(Amount Columns) - I am doing it already

2. While loading from files:

a.Date Formats

b.Need to check if string columns are loading after proper trimming of space etc

c. Few columns we are populating Part of the string , need to validate these

d. we have applied few lookups/applymaps need to check correct data is being populated.

etc

3. while loading from db

a.Few columns we are populating Part of the string , need to validate these

b. we have applied few lookups/applymaps need to check correct data is being populated.

etc

Re: Best way to Validate QVD's

Miguel_Angel_Baeyens — Fri, 22 Jun 2018 07:04:55 GMT

But the key question remains: against what are you going to validate? Is an export to excel from both the DB and the QVD enough? How can you compare, other than visually running a SQL query in the data source (which by the way, might not be even possible if you are using ApplyMap()s or JOINs in the script)?

You can create as many fields with Date() or Len() functions to "measure" how the fields are loaded. But since in QlikView there are no data types, fields like dates or float numbers can be very difficult to compare unless you are using them on a chart applying a very specific format.

You can also use the third parameter in the ApplyMap() function to display a value in case a match is not found in the mapping table, and then individually look up for those values in both the QVD and the DB.

Re: Best way to Validate QVD's

Anonymous — Fri, 22 Jun 2018 07:16:22 GMT

Is an export to excel from both the DB and the QVD enough? How can you compare, other than visually running a SQL query in the data source (which by the way, might not be even possible if you are using ApplyMap()s or JOINs in the script)?

Currently this is what we are doing. As this is becoming time consuming , We are looking for alternatives. Do we have any tools which which help us automate these things.

Re: Best way to Validate QVD's

Anonymous — Fri, 22 Jun 2018 07:19:21 GMT

In Simple - Looking for a tool which allows us to Define some transformations on Source and Compare this with Target QVD

Re: Best way to Validate QVD's

Miguel_Angel_Baeyens — Fri, 22 Jun 2018 07:24:03 GMT

If you already have the objects, NPrinting would be the way to go to automate the extractions as soon as the new data is reloaded.

Other tools would need to be created ad-hoc to address your needs. A lot of people I know are using QlikView applications.

If you mean non-QlikView tools, I don't know of any.

After all, a QVD is nothing else that the raw table stored into yet another XML/binary format. The storing process itself or the file format do not make any changes to the data.