Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
I am going to try that. Thaanks a lot!