API to 1 table help please (data example included)

[#1]

Thanks for helping!

I use an Rest API and get the output below:

I pasted it in an txt file. Put it on google drive and then i loaded it into Qlik Sense.

now Qlik Sense give me the option to load the file as JSON

The problem is it will give me 3 tables, instead of one.

Cols: with ncloc,sqale_index

Cells: with all "d"

V(@Values): with all values (wich should be separated at the , in "v")

What i need is a table with 3 columns, something like this:

date (=d) , ncloc, sqale_index

2013-04-17T01:06:42+0200 , 331029, null

2013-05-26T01:07:28+0200, 346833, null

and so on.

Data comes out the API like this:

[{"cols":[{"metric":"ncloc"},{"metric":"sqale_index"}],"cells":[{"d":"2013-04-12T13:52:34+0200","v":[330197,null]},{"d":"2013-04-17T01:06:42+0200","v":[331029,null]},{"d":"2013-05-26T01:07:28+0200","v":[346833,null]},{"d":"2013-06-23T00:35:27+0200","v":[359706,null]},{"d":"2013-07-04T08:50:53+0200","v":[362215,null]},{"d":"2013-07-26T00:36:26+0200","v":[365966,null]},{"d":"2013-07-31T00:33:19+0200","v":[366160,null]},{"d":"2013-08-08T00:33:22+0200","v":[366738,null]},{"d":"2013-08-09T00:33:47+0200","v":[366900,null]},{"d":"2013-08-10T00:33:54+0200","v":[367274,null]},{"d":"2013-08-21T00:33:16+0200","v":[368819,null]},{"d":"2013-08-26T00:35:54+0200","v":[369098,null]},{"d":"2013-08-28T00:33:11+0200","v":[369397,null]},{"d":"2013-08-30T00:33:21+0200","v":[370877,null]},{"d":"2013-09-11T00:33:11+0200","v":[373582,null]},{"d":"2013-09-18T00:33:12+0200","v":[376258,null]},{"d":"2013-10-11T00:34:46+0200","v":[394612,null]},{"d":"2013-10-16T00:34:46+0200","v":[395787,null]},{"d":"2013-10-21T00:36:15+0200","v":[399630,null]},{"d":"2013-11-07T00:35:10+0100","v":[386894,null]},{"d":"2013-12-02T00:36:54+0100","v":[383708,null]},{"d":"2014-01-03T00:35:30+0100","v":[389446,null]},{"d":"2014-01-17T00:35:27+0100","v":[391232,null]},{"d":"2014-01-31T00:34:56+0100","v":[388220,null]},{"d":"2014-02-24T00:39:56+0100","v":[391965,null]},{"d":"2014-03-13T00:35:36+0100","v":[395417,null]},{"d":"2014-03-17T00:35:37+0100","v":[395922,null]},{"d":"2014-04-11T00:34:51+0200","v":[402827,null]},{"d":"2014-05-14T00:39:48+0200","v":[407589,null]},{"d":"2014-06-06T00:39:48+0200","v":[414934,null]},{"d":"2014-07-21T00:41:50+0200","v":[422677,null]},{"d":"2014-08-29T00:39:38+0200","v":[429613,null]},{"d":"2014-09-01T00:42:01+0200","v":[430507,null]},{"d":"2014-09-29T00:41:51+0200","v":[433657,null]},{"d":"2014-10-27T00:41:08+0100","v":[438079,null]},{"d":"2014-11-24T00:42:04+0100","v":[443149,null]},{"d":"2014-12-04T00:48:05+0100","v":[446862,916035]},{"d":"2014-12-05T00:48:16+0100","v":[447174,907840]},{"d":"2014-12-09T00:47:53+0100","v":[447088,698372]},{"d":"2014-12-29T00:48:44+0100","v":[457797,701402]},{"d":"2015-01-26T00:56:40+0100","v":[470743,713154]},{"d":"2015-02-25T00:51:31+0100","v":[485348,703744]},{"d":"2015-02-27T00:51:27+0100","v":[485099,702316]},{"d":"2015-03-25T01:05:37+0100","v":[493000,688744]},{"d":"2015-03-30T01:06:52+0200","v":[493994,686708]},{"d":"2015-04-27T01:06:37+0200","v":[514259,700444]},{"d":"2015-05-29T00:58:52+0200","v":[546093,855218]},{"d":"2015-06-30T00:59:14+0200","v":[553898,842639]},{"d":"2015-07-17T00:57:54+0200","v":[558971,768247]},{"d":"2015-08-03T00:59:29+0200","v":[562473,767230]},{"d":"2015-08-17T00:58:55+0200","v":[564337,769521]},{"d":"2015-08-24T00:59:26+0200","v":[565598,770038]},{"d":"2015-09-04T00:58:09+0200","v":[568025,769429]},{"d":"2015-09-07T01:02:39+0200","v":[568127,768551]},{"d":"2015-09-14T00:59:45+0200","v":[568706,765514]},{"d":"2015-09-25T00:59:02+0200","v":[572216,769200]},{"d":"2015-09-28T01:00:07+0200","v":[572302,767840]},{"d":"2015-10-05T00:59:11+0200","v":[573530,765652]},{"d":"2015-10-12T00:59:58+0200","v":[574673,763638]},{"d":"2015-10-19T00:59:50+0200","v":[576673,764693]},{"d":"2015-10-27T00:58:44+0100","v":[579764,767833]},{"d":"2015-11-02T01:04:16+0100","v":[581993,765207]},{"d":"2015-11-09T01:00:27+0100","v":[585041,767489]},{"d":"2015-11-20T00:59:28+0100","v":[593151,776877]},{"d":"2015-11-23T00:59:31+0100","v":[593865,777627]},{"d":"2015-12-03T01:00:03+0100","v":[598372,783739]},{"d":"2015-12-07T00:35:56+0100","v":[600325,707177]},{"d":"2015-12-14T01:10:34+0100","v":[602593,786884]},{"d":"2015-12-21T01:06:30+0100","v":[601176,785579]},{"d":"2015-12-28T01:07:23+0100","v":[606204,787909]},{"d":"2016-01-01T01:01:56+0100","v":[606627,788357]},{"d":"2016-01-06T01:04:05+0100","v":[607180,997086]},{"d":"2016-01-11T01:09:14+0100","v":[607631,995118]},{"d":"2016-01-18T01:06:32+0100","v":[609720,995631]},{"d":"2016-01-25T01:07:47+0100","v":[610950,997030]},{"d":"2016-02-01T01:10:21+0100","v":[612191,1074321]},{"d":"2016-02-08T01:09:11+0100","v":[614790,1084006]},{"d":"2016-02-15T01:12:26+0100","v":[615570,1082826]},{"d":"2016-02-22T01:18:55+0100","v":[618631,1090891]},{"d":"2016-03-01T09:28:30+0100","v":[624267,1096227]},{"d":"2016-03-11T01:26:05+0100","v":[626665,1097193]},{"d":"2016-03-15T01:25:37+0100","v":[626829,1080273]},{"d":"2016-03-21T01:30:11+0100","v":[627976,1079670]},{"d":"2016-03-28T01:31:29+0200","v":[628193,1076620]},{"d":"2016-04-05T01:27:45+0200","v":[629662,1077754]},{"d":"2016-04-11T01:33:13+0200","v":[631589,1081341]},{"d":"2016-04-18T01:34:13+0200","v":[632658,1080529]},{"d":"2016-04-25T01:20:25+0200","v":[634089,1078053]},{"d":"2016-05-02T01:34:03+0200","v":[635689,1078511]},{"d":"2016-05-09T01:22:54+0200","v":[636100,1075822]},{"d":"2016-05-20T01:24:43+0200","v":[637697,1059993]},{"d":"2016-05-23T01:28:12+0200","v":[637886,1059318]},{"d":"2016-05-30T01:32:37+0200","v":[639603,1063182]},{"d":"2016-06-06T01:36:32+0200","v":[641649,1065830]},{"d":"2016-06-13T01:40:23+0200","v":[644633,1067260]},{"d":"2016-06-20T01:38:56+0200","v":[648405,1070484]},{"d":"2016-07-01T01:28:58+0200","v":[652761,1068893]},{"d":"2016-07-06T01:17:09+0200","v":[652998,1067786]},{"d":"2016-07-11T01:18:50+0200","v":[654245,1070452]},{"d":"2016-07-13T04:11:21+0200","v":[655202,1071356]},{"d":"2016-07-14T04:21:01+0200","v":[654921,1070909]},{"d":"2016-07-15T05:21:51+0200","v":[656222,1073967]},{"d":"2016-07-18T05:26:55+0200","v":[656320,1072353]},{"d":"2016-07-19T05:28:22+0200","v":[656654,1072054]},{"d":"2016-07-20T05:22:12+0200","v":[657246,1072446]},{"d":"2016-07-21T00:52:28+0200","v":[657293,1072375]},{"d":"2016-07-22T01:25:32+0200","v":[657572,1072593]},{"d":"2016-07-26T01:26:01+0200","v":[657820,1072561]},{"d":"2016-07-27T01:30:07+0200","v":[657851,1072247]},{"d":"2016-07-28T01:34:35+0200","v":[658013,1072401]},{"d":"2016-07-29T01:32:24+0200","v":[658658,1072553]},{"d":"2016-08-01T01:29:28+0200","v":[657784,1071521]},{"d":"2016-08-02T01:25:27+0200","v":[657986,1071682]},{"d":"2016-08-03T01:19:11+0200","v":[658157,1071018]},{"d":"2016-08-04T01:23:54+0200","v":[657614,1070487]},{"d":"2016-08-05T01:26:07+0200","v":[657835,1070437]},{"d":"2016-08-08T01:28:10+0200","v":[658966,1071205]},{"d":"2016-08-09T01:19:19+0200","v":[659049,1071190]}]}]

i found some things as join (outer join)

But that didn't give me the result i wanted as well.

Just can't get it to work, please help me!!

Thank you for your time