SUMIFS in LOAD script

[#1]

Hi,

I have a large straight table with a lot of SUM IF formulas like the below, which makes it very slow when recalculating.

Im thinking about making the calculations in the script instead. Any idea how I could implement it?

Thanks in advance,

Olel

SUM({<[FLOW TYPE]= {'XXX'}>} IF([FLOW EFFECTIVE DATE]<=DATE and [FLOW MATURITY DATE]>= DATE,AMOUNT))

[Gysbert_Wassenaar]

You need a table with all the fields from your chart, the dimensions and the fields you use in the expression. If you create such a table in your load script you can generate a summary table with the sums:

Summary:

LOAD

     Dim1, Dim2, ..., DimN

     sum(if([FLOW TYPE] = 'XXX' AND [FLOW EFFECTIVE DATE] =< DATE AND [FLOW MATURITY DATE] >= DATE, AMOUNT)) as SumOfAmount

RESIDENT ...source_table...

GROUP BY Dim1, Dim2, ..., DimN;

[#3]

Hi Olle,

the other option you have beyond grouping and summing the data in the script, is to create a flag comparing your dates which you can then use in set analysis, which would perform better then a sumif like that.

In script:

If([FLOW EFFECTIVE DATE] =< DATE AND [FLOW MATURITY DATE] >= DATE,1) As Date_Flag

Expression:

Sum({<[FLOW TYPE] = {'XXX'}, Date_Flag={1}>} AMOUNT)

hope that helps

Joe

[#4]

Thanks Gysbert.

Looks like some syntax issue, cannot make it work. Do you see what could be the problem?

Kind Regards,

Olle

[jagan]

Hi,

Try like this

Summary:

LOAD

     Dim1, Dim2, ..., DimN,

     if([FLOW TYPE] = 'XXX' AND [FLOW EFFECTIVE DATE] <= DATE AND [FLOW MATURITY DATE] >= DATE, AMOUNT) as AmountWithIF

RESIDENT DataSource;

Now just use

Sum(AmountWithIF) as expression

Hope this helps you.

Regards,

Jagan.

[#6]

Hi Jagan,

Tried it but no values are stored in AmountWithIF for some reason

//O

[jagan]

Hi,

Is date fields [FLOW EFFECTIVE DATE], DATE, [FLOW MATURITY DATE] are in same format?  Those fields converted into date? Check the format of those.

[#8]

Hi again Gysbert,

the syntax is now working however getting the below error message.

Any clues how to structure the group by?

[Gysbert_Wassenaar]

All the fields NOT used in an aggregation function (like sum) should be part of the GROUP BY clause. In the screenshot you posted I don't see any aggregation function. That's also what the error message means. If you have a group by clause then an aggregation function is required. That aggregation function is missing.

[#10]

Hi again,

when running my script I get the below erorr message. Now I have an aggregation function and I'm grouping by the fields NOT in the aggregation function. Can you see what might still be wrong?

Thanks in avance,

Olle

LOAD     

    [INSERTION DATE],
    TIME,
    [FLOW TYPE],
    AMOUNT,
    date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
    date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
    TEXT
FROM

(ooxml, embedded labels, table is TRANSACTIONS);

left join
LOAD date(Date) as DATE

FROM

(ooxml, embedded labels, table is Sheet1);

FINAL:
LOAD
      [INSERTION DATE],
    TIME,
    [FLOW TYPE],
    AMOUNT,
    date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
    date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
    TEXT,
    SUM(IF([FLOW TYPE] = 'XXX' AND [FLOW EFFECTIVE DATE] <= DATE AND [FLOW MATURITY DATE] >= DATE, AMOUNT)) as AMOUNTIF

RESIDENT TEMP
GROUP BY [INSERTION DATE], TIME,TEXT;
DROP TABLE TEMP