topic Re: Expression in QlikView

Expression

Fri, 14 Oct 2011 07:42:46 GMT

Hi All,

Please Suggest how to write this in expression

Set TempSum=0;

Set RowCount= 162522;

for(RowCount<= 162522)

if(NoOfPallets <> 0)

TempSum= TempSum+ Pallets;

RowCount++;

Thanks.

Expression

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 07:46:39 GMT

Hi Sandeepa,

Is that an expression in a chart? Or a loop in the script? Can you please elaborate on your requirements and expected returns?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Expression

Fri, 14 Oct 2011 08:24:41 GMT

Query is this :

Set TempSum=0;

Let y= noofrows('Data')

for i=1 to $(y)

if(NoOfPallets <> 0)

TempSum= TempSum+ NoOfPallets;

y++;

Expression

SunilChauhan — Fri, 14 Oct 2011 08:29:04 GMT

hope this

Set TempSum=0;

Let y= noofrows('Data')

for i=1 to $(y)

if(NoOfPallets <> 0)

load

TempSum+ NoOfPallets as TempSum

autogenrate 1;

y++;

Re: Expression

Fri, 14 Oct 2011 08:47:57 GMT

Please Correct it:

Data:

LOAD CustNo,

ItemCode,

OrderNo,

OrderQty,

DeliveredQty,

InvoiceDate,

Month([Invoice Date]) as Month,

'Q' & ceil(month([Invoice Date]) / 3) AS Quarter,

InvoiceNo,

InvoiceAmount,

Pallet,

Carton,

Shrink,

[No Of Pallets] as NoOfPallets,

[No Of Cartons] as NoOfCartons,

[No Of Shrinks] as NoOfShrinks,

Loose

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Let y= noofrows('Data');

Join(Data)

Load

for i=1 to $(y)

if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum,

autogenerate 1;

y++;

Expression

SunilChauhan — Fri, 14 Oct 2011 08:53:13 GMT

what to correct its look like ok

take TempSum into expression and see the result

if not please be crear what you want.

Re: Expression

Fri, 14 Oct 2011 09:02:17 GMT

Let y= noofrows('Data');

Join(Data)

Load

for i=1 to $(y)

if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum,

autogenerate 1;

y++;

It's giving syntax error.

Expression

SunilChauhan — Fri, 14 Oct 2011 09:05:38 GMT

use this

Let y= noofrows('Data');

Join(Data)

Load

for i=1 to $(y)

if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum// here comma is not required

autogenerate 1;

y++;

Re: Expression

Fri, 14 Oct 2011 09:15:33 GMT

Still it is giving error.

" y++ Unknown"

Please find attached of the error file.

Expression

SunilChauhan — Fri, 14 Oct 2011 09:21:15 GMT

i believe no need to join because there is no key fieeld

use the below code

Let y= noofrows('Data');

Load

for i=1 to $(y)

if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum// here comma is not required

autogenerate 1;

y++;

hope this help

Re: Expression

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 09:35:14 GMT

Hi,

Why not using Peek()? If you want to get an aggregated value in the script you don't need to loop, you can get it using RangeSum(), Peek(), Previous() and so, as shown in this thread and this post among some others. (Some with working examples).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Expression

SunilChauhan — Fri, 14 Oct 2011 09:35:24 GMT

use the coe below

and reload

Data:

Data:
LOAD [Customer Number] as CustNo,
     [Customer Name] as CustName,
     [Item Code] as ItemCode,
     [Item Description] as ItemDesc,
     [Order Number] as OrderNo,
     [Order Position] as OrderPosition,
     BackOrder ,
     [Order Qty] as OrderQty,
     [Delivered Qty] as DeliveredQty,
     [Invoice Date] as InvoiceDate,
     Month([Invoice Date]) as Month,
    'Q' & ceil(month([Invoice Date]) / 3)    AS Quarter,
     [Invoice Number] as InvoiceNo,
     [Invoice Amount] as InvoiceAmount,
     [Line of Business] as LOB,
     [Line Of Business Description] as LOBDesc,
     Warehouse,
     Pallet,
     Carton,
     Shrink,
     [No Of Pallets] as NoOfPallets,
     [No Of Cartons] as NoOfCartons,
     [No Of Shrinks] as NoOfShrinks,
     Loose
FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Let y= noofrows('Data');
Set TempSum=0;

for i=1 to $(y)
Load
if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum

autogenerate 1;

next i;

try this

Re: Expression

Fri, 14 Oct 2011 09:44:52 GMT

Now it is giving different error " Field not found error" .

" NoOfPallets Field Not found"

Expression

SunilChauhan — Fri, 14 Oct 2011 09:49:39 GMT

what about this

Let y= noofrows('Data');
Set TempSum=0;

for i=1 to $(y)
Load

if(NoOfPallets <> 0, TempSum+ NoOfPallets) as TempSum

Resident Data;

next i;

Re: Expression

Fri, 14 Oct 2011 10:04:29 GMT

Now it is saying "TempSum field is not found".

Re: Expression

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 10:07:04 GMT

Hi,

Did you try mi suggestion below? TempSum is not a field in table "Data", hence the error. Probably using $(TempSum) instead because I'm assuming you are referring to the previously set variable TempSum, isn't it?

Regards.

Re: Expression

Fri, 14 Oct 2011 10:11:35 GMT

Yes, Your right.

I am not very familiar with qlikview . Hence facing difficulties to write the expression using peak(), rangesum().

Can you please tell me how to write the specified query using peak() and rangesum().

Many Thanks.

Expression

SunilChauhan — Fri, 14 Oct 2011 10:13:43 GMT

tempsum is variable which is always use with $ sign

so use $(TempSum)

may be you required to writein single quotes '$(TempSum)'

Re: Expression

Fri, 14 Oct 2011 10:29:23 GMT

Hi Sunil,

Now there is no error.

But it is looping .. Taking long long time.

It will loop till 162522.

Re: Expression

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 11:13:29 GMT

Hi Sandeepa,

Using the following script, a new field called TempSum will be created and it will be populated with the sum of TempSum, taking into account that if NoOfPallets is equal to zero, the value will be 1, otherwise, it will accumulate from previous values.

Data:
LOAD CustNo,
     ItemCode,
     OrderNo,
     OrderQty,
     DeliveredQty,
     InvoiceDate,
     Month([Invoice Date]) as Month,
     'Q' & ceil(month([Invoice Date]) / 3)    AS Quarter,
     InvoiceNo,
     InvoiceAmount,
     Pallet,
     Carton,
     Shrink,
     [No Of Pallets] as NoOfPallets,
     If([No Of Pallets] <> 0, RangeSum(Peek('TempSum'), 1), 1) AS TempSum,
     [No Of Cartons] as NoOfCartons,
     [No Of Shrinks] as NoOfShrinks,
     Loose
FROM
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

However, this field does not make much sense to me as it is, my guess is that you will need the accumulation of NoOfPallets monthly, quarterly or something. Please read and check the working examples and applications in the posts I mentioned below.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica