Read & Store JSON-like web data

gumersindo — Sun, 28 Jun 2015 06:22:35 GMT

Recently I had to convert some web-data with a Separate Comma format like this:

[['2015/05/28',9.000],['2015/05/29',9.005],['2015/06/01',9.010],['2015/06/02',9.015],['2015/06/03',9.020],['2015/06/04',9.020],['2015/06/05',9.025],['2015/06/08',9.030],['2015/06/09',9.035],['2015/06/10',9.040],['2015/06/11',9.045],['2015/06/12',9.050],['2015/06/15',9.055],['2015/06/16',9.055],['2015/06/17',9.060],['2015/06/18',9.065],['2015/06/19',9.070],['2015/06/22',9.075],['2015/06/23',9.075],['2015/06/24',9.080],['2015/06/25',9.085],['2015/06/26',9.090]]

To this style:

Original	RateDateCalculated	RateDateNumber	Amount
[['2015/05/28',9.000	2015/05/28	2015/05/28	9
,['2015/05/29',9.005	2015/05/29	2015/05/29	9,005

With Qlik Sense. 🙂

1-Data Source :

URL: http://www.ambito.com/economia/mercados/monedas/dolar/x_dolar_get_grafico.asp?ric=ARSSCBCRA&tipo=m

Name: Cotizaciones

2-Folder Source:

QVdata folder in my computer.

2-Script

[tmp_rates]:

CrossTable(Param, RateDate)

LOAD AutoNumber([@1]) as AutoN,

[@1],

[@2],

[@3],

[@4],

[@5],

[@6],

[@7],

[@8],

[@9],

[@10],

[@11],

[@12],

[@13],

[@14],

[@15],

[@16],

[@17],

[@18],

[@19],

[@20],

[@21],

[@22]

FROM [lib://Cotizaciones]

(txt, codepage is 1252, no labels, delimiter is ']', no quotes);

//Drop temporal fields.

drop Fields AutoN, Param;

//Create a RatesTable: Convert Date, Date-Number, Convert value to Amount.

Rates:

Load

,mid(RateDate, 4,10) as RateDateCalculated

,Date#(mid(RateDate, 4,10),'YYYY/MM/DD') as RateDateNumber

,num(mid(RateDate, 16, 10), '#.##0,000')/1000 as Amount

resident tmp_rates;

//Drop temporal table

drop table tmp_rates;

//Store Data in a Folder

store Rates into 'lib://QVdata/Rates.qvd' (qvd);

I hope this HELP YOU!! (Or you can improve IT)

Re: Read & Store JSON-like web data

Michael_Tarallo — Mon, 29 Jun 2015 17:46:03 GMT

Nice example Gumersindo, thanks for the contribution.

Please note there is also a vendor QVSource that has a JSON connector that can assist with this.

Regards,

Mike T

Qlik

topic Re: Read & Store JSON-like web data in Connectivity & Data Prep

Read & Store JSON-like web data

Re: Read & Store JSON-like web data