topic Re: How to order Load statements in QlikView

How to order Load statements

spcon2016 — Mon, 25 Sep 2017 12:31:40 GMT

Hello Community,

I would like to understand the logic of ordering Load-Statements.

The following script doesn't work as intended. I'm trying to make a "Rangesum" with the Peek()-function. It looks good at the first glance, but if I try to make a separate Load-statement to order the Sales, an error occurs.

//*3.* Finally, I'm trying to safe the table temporarilly and order by Customer_Aggr

OrderAttemp:

LOAD *

Resident Operations Order By Customer_Aggr;

//*2.* Than I load the data and make the rangesum-operation with peek()

Operations:

Load

Customer_Name,

Customer_Place,

Customer_Sales,

Customer_Sales + Peek(Customer_Sales,-1) AS Customer_Aggr;

//*1.* Here I'm creating my data

ExampleData:

Load * Inline [

Customer_ID, Customer_Name, Customer_Place, Customer_Sales

1,Eric,Boston,450

2,John,Kreta,500

3,Rick,Texas,320

4,Martin,Ohio,250

5,Marcus,Kensas,140

6,Lisa,Boston,659

7,Geo,Kreta,370

8,Monti,Kreta,220

9,Daniel,Texas,180

10,Carl,Kensas,220

11,Adi,Kensas,840

12,Begoli,Ohio,1

13,Bruce,Texas,456

14,Hillary,Kreta,6986];

I did change the order several times, but didn't help. Could anyone explain me the reason why the script doesn't work and the logic of ordering load statements by the way?

Best regards

K.K

Re: How to order Load statements

its_anandrjs — Mon, 25 Sep 2017 12:37:29 GMT

Try this

//*2.* Than I load the data and make the rangesum-operation with peek()

Operations:

Load

Customer_Name,

Customer_Place,

Customer_Sales,

Rangesum( Customer_Sales + Peek(Customer_Sales,-1)) AS Customer_Aggr;

//*1.* Here I'm creating my data

//ExampleData:

Load * Inline [

Customer_ID, Customer_Name, Customer_Place, Customer_Sales

1,Eric,Boston,450

2,John,Kreta,500

3,Rick,Texas,320

4,Martin,Ohio,250

5,Marcus,Kensas,140

6,Lisa,Boston,659

7,Geo,Kreta,370

8,Monti,Kreta,220

9,Daniel,Texas,180

10,Carl,Kensas,220

11,Adi,Kensas,840

12,Begoli,Ohio,1

13,Bruce,Texas,456

14,Hillary,Kreta,6986];

////*3.* Finally, I'm trying to safe the table temporarilly and order by Customer_Aggr

NoConcatenate

OrderAttemp:

LOAD * Resident Operations Order By Customer_Aggr;

DROP Table Operations;

Re: How to order Load statements

spcon2016 — Mon, 25 Sep 2017 13:33:19 GMT

Hi,

your changes don't work:

Operations:

Load

Customer_Name,

Customer_Date,

Customer_Place,

Customer_Sales,

Rangesum( Customer_Sales + Peek(Customer_Sales,-1)) AS Customer_Aggr;

ExampleData:

Load * Inline [

Customer_ID, Customer_Name,Customer_Date, Customer_Place, Customer_Sales

1,Eric,15.04.2015,Boston,450

2,John,18.04.2015,Kreta,500

3,Rick,20.04.2015,Texas,320

4,Martin,22.04.2015,Ohio,250

5,Marcus,25.04.2015,Kensas,140

6,Lisa,28.04.2015,Boston,659

7,Geo,30.04.2015,Kreta,370

8,Monti,07.05.2015,Kreta,220

9,Daniel,10.05.2015,Texas,180

10,Carl,15.05.2015,Kensas,220

11,Adi,18.05.2015,Kensas,840

12,Begoli,20.05.2015,Ohio,1

13,Bruce,24.05.2015,Texas,456

14,Hillary,28.05.2015,Kreta,6986];

NoConcatenate

OrderAttemp:

LOAD *Resident Operations Order By Customer_Aggr;

DROP Table Operations;

Error Alert: Can't find Table Operations.

Re: How to order Load statements

its_anandrjs — Mon, 25 Sep 2017 18:47:10 GMT

PFA with no error.