Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am loading an SQL table with values for Date, Type and Amount. Sometimes I have multiple values for the same Date and Type, but with different amounts. For each Date and Type combo, I'm looking to load only those records that have the Max amount.
Date Type Amount
x a 10
y a 12
z a 8
z a 15
So in this table I only want z, a, 15 to load. Any way I can simply do this by altering my Load script maybe?
MyTable:
SQL SELECT Date, Type, Amount
FROM ...mytable...;
right join (MyTable)
LOAD Type, max(Amount) as Amount;
RESIDENT MyTable
GROUP BY Type;
LOAD
Date,
Type,
Max(Amount) as MaxAmount
Resident Temp1
Group By Date, Type;
Hi,
You can try this also
Raw:
LOAD * INLINE [
Date, Type, Amount
x, a, 10
y, a, 12
z, a, 8
z, a, 15
];
LOAD
Max(Amount) as Amount
Resident Raw;
Left Join
LOAD * Resident Raw;
DROP Table Raw;
Regards
Anand
MyTable:
SQL SELECT Date, Type, Amount
FROM ...mytable...;
right join (MyTable)
LOAD Type, max(Amount) as Amount;
RESIDENT MyTable
GROUP BY Type;
LOAD
Date,
Type,
Firstsortedvalue(Amount,-Amount) as MaxAmount
Group By Date, Type;
Sql Select Date,Type,Amount From Table;
Or
LOAD
Date,
Type,
Max(Amount) as MaxAmount
Group By Date, Type;
Sql Select Date,Type,Amount From Table;
Thanks! I couldn't get it to work at first but when I took the max of a new variable, date and type combined, the right join worked like a charm