Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
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
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;
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;