What is mistake in my script ? My first question .

[prahlad_infy]

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 .

[Peter_Cammaert]

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 · Accepted solution]

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]

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]

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.