Alteryx: To QVX, or Not To QVX
Question
Alteryx is an awesome tool for Data Analysts and can do a lot of things. As of version 10, not only Alteryx can write data to QVX file, but can also read QVX files. It is a bit difficult to find information about QVX format itself on Qlik.com but Alteryx posted some details in their help documentation here. They note in documentation that Read support for QVX files is not available at this time. but it is supported as of version 10.
In a nutshell, QVX format is similar to QVD format and was created as an open format to exchange data with other applications and interfaces. The downside of this format are:
-
All QlikView developers use QVD format and many of them have not even heard about QVX, so you cannot really read QVD files. A lot of organizations would invest a lot of time to build fast QVD layer so without explicit conversion, you won't be able to just read or write to QVDs. This is not Alteryx fault, but Qlik's - QVD is a proprietary format.
-
QVD format supports optimized load to Qlik (fast load of data to Qlik if certain conditions are met). QVX does not support optimized loads.
-
QVX is XML file while QVD is binary (and compressed) so QVD files should take less space.
This brings the question - why would someone write data from Alteryx into QVX file instead of using old-fashioned flat files? Qlik is very good at figuring out the structure of flat files and a lot of times, you do not even need to specify delimiters, headers etc. Is this just a marketing thing to attract QlikView customers to Alteryx?
Answer?
I did a quick test to compare QVX and flat file formats.
I loaded 30 million rows (wide tables with 50-60 fields, mixed numbers, text and dates) from a local database to a tab-delimited text file. Then I loaded that file to a QlikView app. After that I used the same text file again to load data back to Alteryx - this way I could see how well Alteryx and QlikView use both formats to read/write data to.
Having done that, I repeated the test again, but this time using QVX format.
Below you can find my results. This is not a scientific test by any means, but you will get an idea.
| TXT | QVX | |
|---|---|---|
| Can be used as Input | Yes, requires some configuration (delimiter, options etc.) | Yes, no configuration - just point Alteryx to a QVX file. |
| Can be used as Output | Yes, requires some configuration (delimiter, options etc.) | Yes, no configuration - just point Alteryx to a QVX file. |
| Format supports metadata | Only Field Names | Field Names and Data Types Useful so all data type conversions can be done in Alteryx. |
| Time to read 30MM rows from a database and save to a file | 490 seconds | 500 seconds |
| Size of the saved file | 18Gb | 19 Gb (+5%) |
| Time to load the file to QlikView | 300 seconds (+20%) | 240 seconds |
| Time to load the file back to Alteryx (used as Input) | 230 seconds
Got a lot of truncation warnings, had to change Field Length in Alteryx Input tool to 1000 |
285 seconds (+20%)
No configuration changes on Input tool |
| Interoperability with other tools | All tools can read TXT! | Limited to Alteryx and QlikView/Qlik Sense – other tools (e.g. Excel, R, SAS) won’t take QVX data and conversion will be required. |
My takeaway, If I know that data coming of Alteryx will only be used with QlikView or Sense, I will use QVX file. If I am not sure whether any other tools will use a file, I'd probably be safer with TXT format.
Hope it helps a bit!