topic Help in Script in QlikView

Help in Script

Tue, 10 Jan 2012 12:35:57 GMT

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;

Help in Script

vijay_iitkgp — Tue, 10 Jan 2012 12:38:33 GMT

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.

Help in Script

swuehl — Tue, 10 Jan 2012 12:41:35 GMT

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

Help in Script

Tue, 10 Jan 2012 12:43:13 GMT

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

Help in Script

SunilChauhan — Tue, 10 Jan 2012 12:43:58 GMT

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

Help in Script

Tue, 10 Jan 2012 12:49:59 GMT

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

Help in Script

vijay_iitkgp — Tue, 10 Jan 2012 12:52:38 GMT

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

Sum(C) AS C

Resident Table1 Group By A,B;

Hope this will help

Re: Help in Script

ekech_infomotio — Tue, 10 Jan 2012 13:07:41 GMT

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

Help in Script

Tue, 10 Jan 2012 13:18:13 GMT

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.