Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
prahlad_infy
Contributor

What is mistake in my script ? My first question .

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 .

1 Solution

Accepted Solutions
Highlighted
vineetadahiya12
New Contributor III

Re: What is mistake in my script ? My first question .

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

13 Replies
Highlighted

Re: What is mistake in my script ? My first question .

Try this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP & Luminary
MVP & Luminary

Re: What is mistake in my script ? My first question .

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: What is mistake in my script ? My first question .

"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

Highlighted

Re: What is mistake in my script ? My first question .

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP & Luminary
MVP & Luminary

Re: What is mistake in my script ? My first question .

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

Highlighted

Re: What is mistake in my script ? My first question .

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

Highlighted

Re: What is mistake in my script ? My first question .

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
prahlad_infy
Contributor

Re: What is mistake in my script ? My first question .

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 .

Highlighted
MVP & Luminary
MVP & Luminary

Re: What is mistake in my script ? My first question .

Peter,

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