1 Reply Latest reply: Jun 29, 2015 1:46 PM by Michael Tarallo RSS

    Read & Store JSON-like web data

    Gumersindo Rey

      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:


       

      OriginalRateDateCalculatedRateDateNumberAmount
      [['2015/05/28',9.0002015/05/282015/05/289
      ,['2015/05/29',9.0052015/05/292015/05/299,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)