Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted

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

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
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

Highlighted
shekhar_analyti
Valued Contributor

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

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 ;

Highlighted

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

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.