Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
