Using http:

[jillwoodley]

Hi,

I am hoping someone can help me. I used to access my data via an ODBC connection and to ensure i get the latest set of data i used the following code.

SQL SELECT RAmount,
    RBusinessUnit +'|'+ REntityCode as EKEY,
    RBusinessUnit,
    RDataSet,
    RDataSetMonth,
    RDataSetYear,
    REntity,
    REntityCode,
    RMonth,
    RPeriod,
    RYear
FROM gfra.dbo.RRptCosts
//selecting the data for each month that has the maximum dataset
where RDataSet = (Select max(RDataSet)
       From RRptCosts c1
       WHERE c1.RPeriod = RRptCosts.RPeriod);

I now have to use http so have converted the above to a load statement but i do not know how to perform the functionallity highlighted. Using this code i get an error

RRptCosts:LOAD RDataSet,
     RDataSetYear,
     RDataSetMonth,
     RPeriod,
     RYear,
     RMonth,
     RBusinessUnit &'|'& REntityCode as CIKEY,
     RBusinessUnit &'|'& REntityCode as PRKEY,
     RBusinessUnit &'|'& REntityCode as CAKEY,
     RAmount,
     REntityCode,
     REntity,
     RBusinessUnitFROM
[http://web3.unity-is.co.uk/gfra/ExportRRptCosts.html]
(html, codepage is 1252, embedded labels, table is @1)

//selecting the data for each month that has the maximum dataset

where RDataSet = (Select max(RDataSet)
       From RRptCosts c1
       WHERE c1.RPeriod = RRptCosts.RPeriod);

Can anyone help - any assistance greatly appreciated.

[SreeniJD]

Try this:

RRptCosts:LOAD RDataSet,
     RDataSetYear,
     RDataSetMonth,
     RPeriod,
     RYear,
     RMonth,
     RBusinessUnit &'|'& REntityCode as CIKEY,
     RBusinessUnit &'|'& REntityCode as PRKEY,
     RBusinessUnit &'|'& REntityCode as CAKEY,
     RAmount,
     REntityCode,
     REntity,
     RBusinessUnitFROM
[http://web3.unity-is.co.uk/gfra/ExportRRptCosts.html]
(html, codepage is 1252, embedded labels, table is @1)

Temp:

Load

     *,

     Max(RDataset)

resident RRptCosts

group by RPeriod;

Sreeni

[jillwoodley]

Sreeni,

Many thanks for your suggestion, however i cant get the code to work

This is the error i get, below that is the code i have.

Can you help?

Many many thanks,

Jill

[SreeniJD]

Change second part of the script like this..

Temp:

Load Max(RDataSet) as RDataSet1

resident RRptCosts

group by RPeriod;

...

Sreeni

[#5]

Hi, I believe we can't combine LOAD & SQL statements into one Load statement. Please follow below:

// First load Max value from DB.

OLEDB $(connection);

MaxData: // get the Max RDataSet for each Period

Select

     Period,

     max(RDataSet) AS RDataSet
From RRptCosts

Group By Period;

RRptCosts:

LOAD RDataSet,
     RDataSetYear,
     RDataSetMonth,
     RPeriod,
     RYear,
     RMonth,
     RBusinessUnit &'|'& REntityCode as CIKEY,
     RBusinessUnit &'|'& REntityCode as PRKEY,
     RBusinessUnit &'|'& REntityCode as CAKEY,
     RAmount,
     REntityCode,
     REntity,
     RBusinessUnitFROM
[http://web3.unity-is.co.uk/gfra/ExportRRptCosts.html]
(html, codepage is 1252, embedded labels, table is @1)

where exists(RDataSet)// Load only RDataSet from MaxData table

;

Drop Table MaxData;

[maxgro]

RRptCosts:

LOAD RDataSet,
     RDataSetYear,
     RDataSetMonth,
     RPeriod,
     RYear,
     RMonth,
     RBusinessUnit &'|'& REntityCode as CIKEY,
     RBusinessUnit &'|'& REntityCode as PRKEY,
     RBusinessUnit &'|'& REntityCode as CAKEY,
     RAmount,
     REntityCode,
     REntity,
     RBusinessUnitFROM
[http://web3.unity-is.co.uk/gfra/ExportRRptCosts.html]
(html, codepage is 1252, embedded labels, table is @1);

// join and keep by RPeriod the max RDataSet

Right Keep (RRptCosts)

load

  RPeriod,

  max(RDataSet) as RDataSet

Resident RRptCosts

Group by RPeriod

;