Seperating comma seperated values

[Rudolf]

Hi,

loading Data via REST i get comma seperated values.

i need to split them in different rows each with a unice name.

"values":[[1617235200,"44605440"],[1617238800,"47284224"],[1617242400,"1249718272"],[1617246000,"8265728"],[1617249600,"52924416"],[1617253200,"95559680"],[1617256800,"103563264"],[1617260400,"47124480"],[1617264000,"50520064"],[1617267600,"68292608"],[1617271200,"60768256"],[1617274800,"51134464"],

the string i get contains to values  first is a timestamp, second ist the associated value.

so far i divided by using the following:

trace tmp_values;

[tmp_values]:
NoConcatenate LOAD
text(Date(Num(left([@Value],10))/86400+25569, 'DD.MM.YYYY hh:mm:ss')) AS [Timestamp],
text(Date(Num(left([@Value],10))/86400+25569, 'DD.MM.YYYY')) as Timestamp_max,
'$(vVE_Name)' as %Key_VE_Name,
'$(vMONAT)' as Monat,
[__FK_values_u0]&'_'&$(iii)&'_'&$(ii) as [__KEY_values]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_values_u0]) AND ([@Value] >=1600000000) AND ([@Value] <=1700000000)
;

trace Inner Join 0;

Inner Join
Load
num#([@Value], '#,##0', '.', ',') AS $(vMetrik_Prom),
[__FK_values_u0]&'_'&$(iii)&'_'&$(ii) as [__KEY_values]
Resident RestConnectorMasterTable
Where ([@Value] <=1600000000) or ([@Value] >=1700000000)
;

The problem ist i now get values wich are in the same range than the timestamps.

Has anyone an idea?

Thanks for your advice

[stevejoyce]

I would try to loop through each of your items in your outside array.  You can do this by 1st counting number of items in the array with substringcount and subfield.  something like:

set vString = "values":[[1617235200,"44605440"],[1617238800,"47284224"],[1617242400,"1249718272"],[1617246000,"8265728"],[1617249600,"52924416"],[1617253200,"95559680"],[1617256800,"103563264"],[1617260400,"47124480"],[1617264000,"50520064"],[1617267600,"68292608"],[1617271200,"60768256"],[1617274800,"51134464"]

let vItemCount = Substringcount('$(vString)', '],[') + 1.

Your item loop will be

for i = 1 to vItemCount 

let vTimestamp = subfield($(vItemCount), ',' 1);

let vValue = subfield($(vItemCount), ',' 2);

tbl:

load:

$(vTimestamp) as Timestamp,

$(vValue) as Value

Autogenerate(1)

;

next i

[Rudolf]

**bleep**, i did not tell everything.... i alredy have a "for" loop in that dataload script, one run will deliver about 6000 to 10000 values... in Final version this loop will run something in between 50 to 100 times.

i will try to solve that.

Thank you, i will give it a try.

[Rudolf]

i am still working on this... creating a list of values that with dynamic content is one idea.