Solved: Re: What is mistake in my script ? My first questi... - Qlik Community

prahlad_infy · ‎2018-01-19

Hello community ,

This is my first question . I am college pass out and new to qlik .Started learning SQL and Qlikview parallely .

Thank you in advance .

vineetadahiya12 · ‎2018-01-23

Hi Prahlad,

The basic Group By rule is all the columns which are not part of Aggregate Function should be listed in Group By. You missed few like Launch date in your script. Hence, getting the error. Further, you can include what Rob suggested in his response.

Below is the script, that will work.

LOAD Client,

Launch_Date,

Date#(Text(Launch_Date),'DD/MM/YYYY') as Date_of_Launch,

if(IsNull(Date#(Text(Launch_Date),'DD/MM/YYYY')),'NULL',Date#(Text(Launch_Date),'DD/MM/YYYY')) AS dt,

Date(Date#(Launch_Date,'DD/MM/YYYY')) as dt2,

Product,

Sub_Product,

Metric,

sum(Value)

FROM

[Client Product.xlsx]

(ooxml, embedded labels, table is Product)

Group by Client, Launch_Date,Product,Sub_Product,Metric ;

View solution in original post

Anil_Babu_Samineni · ‎2018-01-19

Try this?

Due to Group By functionality should written only for Resident tables not the loading tables

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

rwunderlich · ‎2018-01-19

When you use group by, each field must either be:

1. Created by an aggregation function, ie Sum().

2. List in the Group By.

If a field is not included as above, then the load does not know how to handle the field. For example you have

Group by Client,Product,Metric,Sub_Product

You also have a field Launch_Date. Which Launch_Date should the load keep? You can fix this using an aggregation like:

min(Launch_Date) as Launch_Date // Keep earliest date

-Rob

rwunderlich · ‎2018-01-19

"Due to Group By functionality should written only for Resident tables not the loading tables"

This is not a restriction I'm aware of. Can you explain further?

-Rob

Anil_Babu_Samineni · ‎2018-01-20

Sure rwunderlich

Let's consider file like

Load Name, Sum(Sales) as Sales From Table Group By Name;

Instead, We need to load using Resident to call all aggregate function

Name:

Load Name, Sales From Table;

Final:

NoConcatenate

Load Name, Sum(Sales) as Sales Resident Name Group By Name;

Drop Table Name;

Note - Correct me if i misunderstand the concept.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

rwunderlich · ‎2018-01-20

This should work as is:

Load Name, Sum(Sales) as Sales From Table Group By Name;

Assuming "Table" is a file or some other source.

"Order By" is only allowed in a Resident Load, but to my knowledge "Group By" is allowed in any load.

-Rob

Peter_Cammaert · ‎2018-01-20

The tricky question is: do we really want to perform a GROUP BY on a file source?

For some added confusion, see here: Optimize Group By Performance

Anil_Babu_Samineni · ‎2018-01-20

The vast volume of data set it is taking too much time while loading First source using Group By like we talk. Instead, I followed using Resident and it reduce some time for reload. Yes, As peter stated i even confused why Order By and along Group By makes more time save.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

prahlad_infy · ‎2018-01-22

Thank you Anil .

But it will be real help , if you can paste me the script . I am using personnel edition , i do not have license .

Also based on ongoing discussion , i am little confused .

What is understand , that we cannot use group during load , but we can used group by if we take resident to initially loaded data .

Please correct me , if i am wrong .

rwunderlich · ‎2018-01-22

Peter,

I'm really sorry you threw this in. Confusion, yes

What is mistake in my script ? My first question .