Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
saadhu77
Contributor II
Contributor II

Best way to Validate QVD's

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..

8 Replies
kaushiknsolanki
Luminary Alumni
Luminary Alumni

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
saadhu77
Contributor II
Contributor II
Author

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

Miguel_Angel_Baeyens

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.)?

saadhu77
Contributor II
Contributor II
Author

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

Miguel_Angel_Baeyens

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.

saadhu77
Contributor II
Contributor II
Author

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.

saadhu77
Contributor II
Contributor II
Author

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

Miguel_Angel_Baeyens

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.