Qlik Community

Catalog and Lineage

Discussion Board for collaboration around Catalog and Lineage.

Announcements
June 21, 10AM ET: Q&A with Qlik, Live Chat! Qlik Lineage for your Data Dynasty. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
lachlanwwells
Partner - Contributor III
Partner - Contributor III

Ugly/Bad/Good data

Hi there,

We are just testing out the QDC For QVDs release and are coming across an issue when trying to view stats on our entities.

All QVDs we import list all fields/columns as 'Ugly' data

(i.e. 9 Total Record Count 0 Good Record Count 0 Bad Record Count 9 Ugly Record Count)

We have looked through the user guide but limited information is noted in regards to what defines 'ugly' data

"Ugly Records
(field data is
problematic)"

For reference, the QVD is a simple table with 9 rows and 3 fields.

Annotation 2020-01-17 130739.png

Annotation 2020-01-17 13f0739.png

 

Any assistance would be very much appreciated.

Kind Regards,

Lachlan

Labels (2)
9 Replies
lachlanwwells
Partner - Contributor III
Partner - Contributor III
Author

So I have an update for those playing at home....

It seems if we use the STORE command from a table created from an Excel spreadsheet - we get the above 'Ugly' data and QDC is unable to read(?) the data

We need to Load the data from Excel into Qlik, then STORE into CSV, then Load the data again from the CSV and then STORE into QVD, and QDC can then read it successfully.

Unsure if this is a bug or if Qlik Core (which performs the internal QDC QVD to CSV conversion) requires some kind of driver? I always assumed QVD was a a standalone format, not a container. I can't understand why the original source (coming into Qlik) makes a difference on the layout/formatting/codepage/structure of the QVD file.

Rodj
Luminary Alumni
Luminary Alumni

I haven't played with QDC yet (it's next on my list when I get some time!) but what you are describing does sounds pretty odd. I always assumed that once the data was in QVD format that the header and content were standardised on what worked for the QIX engine. What happens if you save the original excel file as a CSV and then load / create a QVD? I assume the original excel file is loading OK and the data is usable. Is there any difference in the field tags created by the engine when it loads the data? That might provide a clue. Hopefully someone more enlightened than I responds!

treysmithdev
Luminary Alumni
Luminary Alumni

Does doing a resident load produce the same result?

Also, have you checked the XML headers of the QVDs and compared the values from the two scenarios. My guess is the data lineage header is the only thing that is different. 

Blog: WhereClause   Twitter: @treysmithdev
Christopher_Ortega

Thank you for sharing this.  We are looking into this specific use case now.  Stay tuned.  In the meantime...

An ugly record means that one or more fields fails a validation.  The online help (accessed in the upper right under "Support") can give you a wealth more details.  I encourage you to explore. 

At a high-level, here is the description.

These records may match the record format but some of the field data is problematic. Ugly records are configurable but include data that don't match the field datatype. Examples include invalid data formats; data containing invalid or unprintable characters, or data that don't match a user-specified pattern or regular expression.

In your specific case, it would appear that there is a datatype associated with the field that QDC is seeing data for which does not match.

Screen Shot 2020-01-17 at 10.24.51 AM.png

 

The above is an example.  Please stay tuned.  As I said, we are investigating your particular case now.  Can you tell me which release of QDC for QVDs you are using?

Thanks!

 

 

lachlanwwells
Partner - Contributor III
Partner - Contributor III
Author

Thanks @Christopher_Ortega - no problem, we were concerned we were doing something incorrectly but hopefully we can get to the bottom of things!

The internal help is great - finalised the Partner learning module for QDC so have a bit of a better understanding of everything

We are using QDC version September (currently having issues with December edition so have a support ticket in!)

 

lachlanwwells
Partner - Contributor III
Partner - Contributor III
Author

@treysmithdev Haven't checked resident load yet - will add that to my list!

But strangely enough this - 

Left is the 'working' QVD and right is 'broken' QVD
As you can note, the working QVD header seems to note 'Unknown' whereas the broken one provides more specific (and more accurate re: data)

 

qvds-diffe.png

 

maksim_senin
Partner - Creator III
Partner - Creator III

@lachlanwwells Hi, did you manage with this?

 

Just faced the same with XML and XLSX data - all records are loaded as Ugly. I loaded XLSX data successfully before, but what can be a reason this time - no thoughs. QDC is wordy about this neither in help no in logs.

 

Have someone a clue?

 

The only obvious difference is a language - successfuly loaded data is 100% English, now I need to load mix of English and Russian data...

maksim_senin
Partner - Creator III
Partner - Creator III

Yep! It does not load data with Russian characters despite the fact the XML has UTF-8 encoding.

 

Meanwhile I'm digging maybe someone has a solution for cyrillic support.

 

Many thankx!

maksim_senin
Partner - Creator III
Partner - Creator III

(updated & updated once again)

1) Found solution for XML source (it's FirstGen XML + XSD) - in source properties added two parameters default.field.allow.control.chars and default.field.allow.non.ascii.chars with true value for both.

maxim_senin_0-1618404700618.png

Data with Russian chars is loaded successfully now. Honestly speaking it's not obvious and I'm not sure this is canonically correct solution, so it would be a good thing to hear Qlik's comments, if any since the page - https://help.qlik.com/en-US/catalog/February2021/Content/QlikCatalog/Source/Qlik_Catalog_Source_Prop... - describes the settings in the "COBOL PROPERTIES" section :).

 

2) The same works for XLSX data 😉

3) And the same must be done for entity created via Transform of Dataflow... more than unobvious, but it seems we need a manner to define default values for some entity settings and I'm not sure this is possible out-of-the-box

Thankx.