Array Pair Loading from databases using Qlik Sense

Question

Does anyone has experience how to load/prepare data:

[(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd')]

taken from SQL database (stored there as value) into qlik sense table:

ID,   Value
1,        a
2,        b
3,        v
4,        d

Answer 1

Check out the annotated script below.

After its execution the result table will be:

set vSQLData = [(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd')];

SQLData:
Load
  // at this point the data will look like: "1, a", "2, b"
  // will split the string on "," and will
  // get the first value as ID
  // and the second one as Valule
  SubField(TempField2, ',', 1) as ID,
  SubField(TempField2, ',', 2) as Value,
;
Load 
  // split the string by ")," and generate N number of rows
  // then for each row remove "(", ")" and "'" characters
  PurgeChar(SubField(TempField1, '),'), '''()''') as TempField2
;
Load
  // remove "[" and "]" characters
  PurgeChar('$(vSQLData)', '[]') as TempField1
AutoGenerate(1)
;