Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prahlad_infy
Partner - Creator II
Partner - Creator II

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 .

13 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

vineetadahiya12
Contributor III
Contributor III

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 ;

shekhar_analyti
Specialist
Specialist

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 ;

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.