topic Using http: in QlikView

Using http:

jillwoodley — Tue, 13 Oct 2015 08:41:13 GMT

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.

Re: Using http:

SreeniJD — Tue, 13 Oct 2015 09:35:10 GMT

Try this:

Temp:

Load

Max(RDataset)

resident RRptCosts

group by RPeriod;

Sreeni

Re: Using http:

jillwoodley — Wed, 14 Oct 2015 11:23:55 GMT

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

Re: Using http:

SreeniJD — Wed, 14 Oct 2015 16:27:26 GMT

Change second part of the script like this..

Temp:

Load Max(RDataSet) as RDataSet1

resident RRptCosts

group by RPeriod;

...

Sreeni

Re: Using http:

Wed, 21 Oct 2015 16:43:48 GMT

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;

Re: Using http:

maxgro — Wed, 21 Oct 2015 16:53:10 GMT

RRptCosts:

// join and keep by RPeriod the max RDataSet

Right Keep (RRptCosts)

load

RPeriod,

max(RDataSet) as RDataSet

Resident RRptCosts

Group by RPeriod

;