Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table where DATE is MM/DD/YYYY:
CODE | DATE | QTY |
---|---|---|
111 | 05/05/2017 | 30 |
111 | 05/03/2017 | 55 |
333 | 08/03/2017 | 18 |
What I need is to create a field named KEY to SUM by Script (with Group By) each combination with CODE and DATE (YearMonth):
So I should get the following:
KEY | QTY |
---|---|
111|201705 | 85 |
333|201708 | 18 |
Do you know how could I do that?
Note: I should have a single KEY value with summarized QTY.
Thank you!!!
This is what you need:
aaa:
LOAD * Inline [
CODE, DATE, QTY
111, 05/05/2017, 30
111, 05/05/2017, 55
333, 08/03/2017, 18
];
BBB:NoConcatenate
LOAD CODE & DATE as KEY, Sum(QTY) as QTY Resident aaa Group By CODE,DATE;
DROP Table aaa;
Hi,
you need to create a Month field from your date field and make a group by on it
This is what you need:
aaa:
LOAD * Inline [
CODE, DATE, QTY
111, 05/05/2017, 30
111, 05/05/2017, 55
333, 08/03/2017, 18
];
BBB:NoConcatenate
LOAD CODE & DATE as KEY, Sum(QTY) as QTY Resident aaa Group By CODE,DATE;
DROP Table aaa;
Try this :
Temp:
LOAD * INLINE [
CODE,DATE,QTY
111,05/05/2017,30
111,05/03/2017,55
333,08/03/2017,18
];
NoConcatenate
Temp_2:
Load
CODE,
text(Date(Date#(DATE,'MM/DD/YYYY'),'YYYYMM')) as DATE,
QTY
Resident Temp;
Drop Table Temp;
NoConcatenate
Final:
Load
CODE&'|'&DATE as KEY,
sum(QTY) as QTY
Resident Temp_2
Group by CODE,DATE;
Drop Table Temp_2;
Hi Alessandro,
his first two lines are different on the Day part..
so you can't group by these columns, right ?
Yes, you are right, I changed the date because I thing there wa a mistake, infact the result you need works with changed date.
If dates are really differen maybe you need aggragation by Code and Year+Month date?
Let me know
Try this :
Temp:
LOAD * INLINE [
CODE,DATE,QTY
111,05/05/2017,30
111,05/03/2017,55
333,08/03/2017,18
];
NoConcatenate
Temp_2:
Load
CODE,
text(Date(Date#(DATE,'MM/DD/YYYY'),'YYYYMM')) as DATE,
QTY
Resident Temp;
Drop Table Temp;
NoConcatenate
Final:
Load
CODE&'|'&DATE as KEY,
sum(QTY) as QTY
Resident Temp_2
Group by CODE,DATE;
Drop Table Temp_2;