Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Parsing flat text with format field="value" in QlikView SQL

I'm bumping into QlikView's limited ETL functionality while looking for a way to parse the following data format :

field1=value1 field2="value 2" field3=value

field1=value2 field3=value

field1=value3 field2="another value" field4="yet another value"

-> All records are one line

-> Values with more than one word are quoted

-> Not all lines have all values, so just using subfield() is not going to do it

-> If it's not doable in QlikView script alone (I found no solution to this problem to date) I can live with an external script that can be triggered at load that "normalizes" the data in a format QlikView can parse correctly

Looking forward to your input.

2 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Table:

GENERIC

LOAD ID, SubField(FieldValue,'=',1) as Field,  SubField(FieldValue,'=',-1) as Value;

LOAD RecNo() as ID, subfield(replace(Line, ' field','█field'),'█') as FieldValue INLINE [

  Line

    'field1=value1 field2="value 2" field3=value'

    field1=value2 field3=value

    'field1=value3 field2="another value" field4="yet another value"'

];

And if you think you can live only with one resulting table you can read this blog post: The Generic Load


talk is cheap, supply exceeds demand
Not applicable
Author

Surprisingly elegant solution Gysbert, I can almost wrap my head around it.

However, I'm not 100% how this would work when :

-> I'm not working with an inline table but I load the data from a file on disk

-> field1, field2, field3 are not the actual field names but can be any descriptive text string that is one word.