Create Key with Code and YearMonth

[microwin88x]

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!!!

[YoussefBelloum]

Hi,

you need to create a Month field from your date field and make a group by on it

[alexandros17 · Accepted solution]

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;

[jaumecf23]

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;

[YoussefBelloum]

Hi Alessandro,

his first two lines are different on the Day part..

so you can't group by these columns, right ?

[alexandros17]

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

[jaumecf23]

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;