Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have several columns in Postgres with an 'Array' datatype and need to present these values as columns in Qlik. After some research, there does not appear to be support for this datatype. The string needs to be parsed. I'm using the subfield. Is this the best and most efficient way to do this ?
The array field value = {v1,v2,v3,v4,...}
Subfield(Mid(v1,1),',',1) as v1,
Subfield(v2,',',2) as v2,
Subfield(v3,',',3) as v3,
Need to remove/exclude the curly braces and the value count is variable so needs to be dynamic.
Any comments/hints ?
Thanks,
Mark
If you want all values in a single field, you could try like:
Load
SubField( PurgeChar(YourField, '{}'), ',') as NewField
From <>;
whenever you are talking about array, Are you created the DB in postgresql from JSON. Can you elaborate more with the field and few values which demonstrates the output
Sure so the table in postgres looks like this 3 array columns. For example the second row is the Start Date so I'm looking to transpose each date into a column inside the {}.
"mr1";
"{2015-12-01,2015-12-02,2015-12-03,2015-12-04,2015-12-05,2015-12-06,2015-12-07,2015-12-08,2015-12-09,2015-12-10}";
"{2016-12-01,2016-12-02,2016-12-03,2016-12-04,2016-12-05,2016-12-06,2016-12-07,2016-12-08,2016-12-09,2016-12-10}";
"{mfn0,mfn1,mfn2,mfn3,mfn4,mfn5,mfn6,mfn7,mfn8,mfn9}"
Thanks
Mark