Expression

[#1]

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.

[Miguel_Angel_Baeyens]

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

[SunilChauhan]

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

[#4]

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

" NoOfPallets Field Not found"

[SunilChauhan]

what about this

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

Resident Data;

next i;

[#6]

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

[Miguel_Angel_Baeyens]

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.

[#8]

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.

[SunilChauhan]

tempsum is variable which is always use with $ sign

so use $(TempSum)

or

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

[#10]

Hi Sunil,

Now there is no error.

But it is looping .. Taking long long time. 

It will loop till 162522.

[Miguel_Angel_Baeyens]

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