topic Re: Qlik Sense Load array pairs from database. in Connectivity & Data Prep

Qlik Sense Load array pairs from database.

pkonderla — Fri, 22 Oct 2021 10:25:37 GMT

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

Thanks

Re: Qlik Sense Load array pairs from database.

Digvijay_Singh — Fri, 22 Oct 2021 12:28:38 GMT

Not sure but subfield(Fieldname,',') can help in creating separate row for each combination and then later you can separate each combination into ID and Value.

Thanks,

Re: Qlik Sense Load array pairs from database.

Digvijay_Singh — Fri, 22 Oct 2021 12:50:19 GMT

Something like this, but I guess you might struggle a bit in handling few chars like '[ ]' in your input

Load ID,PurgeChar(Value,CHR(39)) as Value1
;
Load subfield(input1,',',1) as ID,
subfield(input1,',',2) as Value,
;
Load purgechar(subfield(input,'),'),'()[]') as input1;
Load * inline [
input
"(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd')"
];

Re: Qlik Sense Load array pairs from database.

pkonderla — Mon, 25 Oct 2021 06:45:15 GMT

Works like charm, thanks...