Postgres Array Datatype

[Anonymous]

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

[tresesco]

If you want all values in a single field, you could try like:

Load

          SubField( PurgeChar(YourField, '{}'), ',') as NewField

From <>;

[Anil_Babu_Samineni]

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

[Anonymous]

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