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

What is mistake in my script ? My first question .

prahlad_infy — Wed, 25 Nov 2020 16:16:04 GMT

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 .

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

Anil_Babu_Samineni — Fri, 19 Jan 2018 18:24:00 GMT

Try this?

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

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

rwunderlich — Fri, 19 Jan 2018 19:02:32 GMT

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

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

rwunderlich — Fri, 19 Jan 2018 19:03:14 GMT

"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

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

Anil_Babu_Samineni — Sat, 20 Jan 2018 05:17:25 GMT

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.

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

rwunderlich — Sat, 20 Jan 2018 16:09:33 GMT

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

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

Peter_Cammaert — Sat, 20 Jan 2018 20:47:16 GMT

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

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

Anil_Babu_Samineni — Sun, 21 Jan 2018 03:32:23 GMT

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.

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

prahlad_infy — Mon, 22 Jan 2018 08:01:35 GMT

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 .

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

rwunderlich — Tue, 23 Jan 2018 00:02:20 GMT

Peter,

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

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

Peter_Cammaert — Tue, 23 Jan 2018 08:58:59 GMT

I'll never do it again

The thing is that to me Anil has a point. While not technically impossible to perform a GROUP BY on a file source, in many cases it is not in the best interest of optimal performance. As far as I can see, I mean.

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

vineetadahiya12 — Tue, 23 Jan 2018 11:18:18 GMT

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 ;

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

shekhar_analyti — Fri, 02 Mar 2018 13:07:52 GMT

Hi Rob ,

I am confused as well ..

Below code does not work

RT:

LOAD Client,

Date(Date#(Launch_Date,'DD/MM/YYYY')) AS Date,

Product,

Sub_Product,

Metric,

Value

FROM

[Client Product.xlsx]

(ooxml, embedded labels, table is Product);

NoConcatenate

RT2:

LOAD Client,

Date,

Product,

Sub_Product,

Metric,

Value,

sum(Value),

min(Date)

Resident RT

Group by Client, Date,Product,Sub_Product,Metric Order BY Client ;

DROP Table RT ;

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

Peter_Cammaert — Fri, 02 Mar 2018 13:26:58 GMT

It should be obvious if you look closely.

In your second LOAD statement, field Value is both specificed on its own and embedded in a Sum() call. Either

add Value to the GROUP BY clause and drop the Sum(Value) call (doesn't make any sense to me, but maybe it does to you), or
drop the bare Value field name from the column list. You will then aggregate all Value values for each combination of values for the GROUP BY fields.