Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
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.)?

Anonymous
Not applicable
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.

Anonymous
Not applicable
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.

Anonymous
Not applicable
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.