Help in Script

[#1]

Hello,

As I am trying a script which is not working:

Could you please advise me where I am making mistake

Load

DeptNo,

DeptSub,

DeptLoc,

ClientName,

Address,

Sum(Freight) as TotFreight,

Amount,

If(TotFreight >0, 'Invoice', 'CreditNote') as InvoiceType,

Qty;

Select *

from SalesTable group by DeptNo, DeptSub, DeptLoc;

[vijay_iitkgp]

Hi You are using aggregation without using group by.

You can first load the table and do the transformation and then use aggregation (Sum) on resident load.

Hope this will help.

[swuehl]

I think there are two issues:

a) TotFreight is not generally known in the Load (the new name is only known after the load is done).

So you need to use

...

If(sum(Freight) >0, 'Invoice', 'CreditNote') as InvoiceType,

...

b) if you are using group by clause, you need to use aggregation functions for all fields that are not listed with the group by, e.g. Client Name, Address, Amount, Qty.

How do you want to aggregate this fields?

Hope this helps,

Stefan

[#4]

Dear Vijay,

Thanks for your reply but I am using group by clause see the below and when I run it hangs the computer.

SQL Select *

from SalesTable group by DeptNo, DeptSub, DeptLoc;

Regards

Joe

[SunilChauhan]

you need to put all the fields inside group by expcept the field which is inside aggregate function

i.e  Freight which is in side sum.

hope this helps

[#6]

Dear Stefan

Thanks for your reply.

As per your point when I try

If(sum(Freight) >0, 'Invoice', 'CreditNote') as InvoiceType, when I reload its silent for long time and hangs.

For the point (b) it contains many field names so that is not possible.

Regards

Joe

[vijay_iitkgp · Accepted solution]

Hi Joe,

This is not correct aggregation .

You can not do Select * from Sales Table Group by DeptNo,DeptSub,DeptLoc;

First Select the columns you require from SQL:

Table1:

Select

A,B,C....

From Sales Table.

After that find the field  which you need to aggregate Group by the fields you want .

Eg:

Load

A,

B,

Sum(C) AS C

Resident Table1 Group By A,B;

Hope this will help

[ekech_infomotio]

you could try the following:

in thte first step load everything to a new table without aggregation and create a new keyfield (%DeptNo%DeptSub%DeptLoc)

Sales:

load

     *,

     DeptNo & '\' & DeptSub & '\' &DeptLoc     as %DeptNo%DeptSub%DeptLoc

Select *

from SalesTable;

In the second step aggregate your data following the rules that every used field must be either contained within your group by statement or in any sort of aggregation:

SalesAggregated:

load

     %DeptNo%DeptSub%DeptLoc,

     sum(Freight)     as TotFreight,

     sum(Amount)     as TotAmount,

     sum(Qty)          as TotQty,

     if(sum(Freight) > 0, 'Invoice', 'CreditNote')      as InvoiceType

resident

     Sales

group by

     %DeptNo%DeptSub%DeptLoc

;

And please read about the basics of aggregation in SQL and/or Qlikview.

greetings,

Edgar

[#9]

Hi Joe.

It is not correct the use of the group by.

SQL Select *

from SalesTable group by DeptNo, DeptSub, DeptLoc; ---> Not valid

Try the following

Sales:

Load

DeptNo,

DeptSub,

DeptLoc,

ClientName,

Address,

Freight,

Amount,

TotFreight,

Qty;

Select *

from SalesTable

Strore Sales  Into

Drop Sales

Sales1:

DeptNo,

DeptSub,

DeptLoc,

ClientName,

Address,

Sum(Freight) as TotFreight,

Amount,

If(TotFreight >0, 'Invoice', 'CreditNote') as InvoiceType,

Qty

FROM (qvd)

group by DeptNo,DeptSub,DeptLoc,ClientName,Address,Amount,InvoiceType,,Qty

Strore Sales1 into

Hope this will help.

Regards.

Ivan.