what is wrong in this load script ?

[shekhar_analyti]

Hi All ,

What is wrong with this Load Script ?

Table1:

LOAD 

     SSN,

     COUNTRY,

     CITY,

     SALES,

     LOSS,

     DRUG,

     COMPANY,

     NAME,

     Max(LOSS) as Los_m 

FROM

SALESSSS.xlsx

(ooxml, embedded labels, table is data)

Group By COUNTRY ;

[marcus_sommer]

You make an aggregation-load - because of your max(LOSS) - but within the group by statement is only Country mentioned whereby all fields which arent included within the aggregation must be listed there.

- Marcus

[vishsaggi]

As Marcus suggested you need all fields in your Group By if you use any aggregation function including different fields. What exactly you want here? You want Max Loss amount for Each country?

May be try like:

Table1:

LOAD 

     SSN,

     COUNTRY,

     CITY,

     SALES,

     LOSS,

     DRUG,

     COMPANY,

     NAME

FROM

SALESSSS.xlsx

(ooxml, embedded labels, table is data);

// LEFT JOIN (Table1) -> Do this as an optional.

Table2:

LOAD COUNTRY, Max(LOSS) as Los_m 

Resident Table1

Group BY

     COUNTRY;

[Anil_Babu_Samineni]

You must required aggregate functions with resident here. Try below

Table1:

LOAD SSN,

     COUNTRY,

     CITY,

     SALES,

     LOSS,

     DRUG,

     COMPANY,

     NAME

FROM

SALESSSS.xlsx

(ooxml, embedded labels, table is data)

NoConcatenate

Load COUNTRY, Max(LOSS) as LOSS Resident Table1 Group By COUNTRY Order By SSN;

Left Join

LOAD * Resident Table1;

DROP Table Table1;