topic Re: Making a group by SUM() in the loading script per month in QlikView

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

Anonymous — Mon, 29 Oct 2012 15:59:43 GMT

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.

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

swuehl — Mon, 29 Oct 2012 16:29:52 GMT

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;

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

Anonymous — Mon, 29 Oct 2012 21:30:54 GMT

I am going to try that. Thaanks a lot!