Making a group by SUM() in the loading script per month

[Anonymous]

Hi there,

I would like to sum the field Amount per Month in the loading script.

For example I got these field

Product

Date

YearMonth (='YYYYMM' created from the field Date)

Amount

I would like to SUM all the amounts per YearMonth, in the script.

This should create new fields with the total for every month that is loaded. (Amount201101, Amount201102, etc)

How could I do this?

Thanks,

Dennis.

[swuehl]

Dennis,

you can use a group by clause and aggregation functions to get your sum of amounts per Month. Then use generic load and joins to create a crosstable from that:

INPUT:

LOAD *, Date(Monthstart(Date),'YYYYMM') as Month;

LOAD

chr(65+floor(RAND()*10)) as Product,

RAND()*100 as Value,

Date(MakeDate(2012)+floor(RAND()*366)) as Date

AutoGenerate 1000;

TMP:

Generic LOAD Product, 'Amount'&Month as Month, sum(Value) as SummedValue Resident INPUT Group by Product, Month;

Result:

load distinct Product resident INPUT;

drop table INPUT;

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) as Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'TMP.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN (Result) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

drop table TableList;

[Anonymous]

I am going to try that. Thaanks a lot!