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

[stabben23]

Hi Olle,

if you use aggregation inside script you also need to have all the other field group by. In your example you also need to group by these fields date([FLOW EFFECTIVE DATE]), date([FLOW MATURITY DATE])

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, date([FLOW EFFECTIVE DATE]), date([FLOW MATURITY DATE]);
DROP TABLE TEMP

[#3]

Hi Staffan!

I tried your solution and also added AMOUNT and DATE, since these are also used in the aggregated function.

Still got the very same error message though

[stabben23]

Not Amount

[swuehl]

You are using FLOW TYPE in your LOAD without Aggregation, but this field is not put in your GROUP BY clause.

I may misunderstood what you want to achieve, but I would suggest that you step back and rethink what you want to do. To me, grouping by e.g. the four date fields and Amount doesn't look right to me (given your Aggregation and what I assume you want to do).

Which of all these fields have you used in your large straight table in the front end as dimension?

[jagan]

Hi,

Try this

LOAD
      [INSERTION DATE],
    TIME,
    [FLOW TYPE],    
    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,

Sum(AMOUNT) AS AMOUNT

RESIDENT TEMP
GROUP BY [FLOW TYPE], [INSERTION DATE], TIME,TEXT, [FLOW EFFECTIVE DATE], [FLOW MATURITY DATE];

[#7]

Hi,

Since my request might have been a bit confusing I've put together an exempel app.

Im looking at transaction data where each transaction ("flow") has a start date and a maturity date.

For each open market date I need to calculate the total balance for each type of transaction,

meaning sum (if  (START_DATE < MARKET DATE < MATURITY DATE )AMOUNT).

Since I have loads of TYPES and rows as well as additional calculations based on the sumifs, my straight table is super slow.

For that reason I need to calculate these SUMIFS in the scrips instead. To speed things up in my tables/charts.

Thanks in advance,

Olle

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#9]

Wow looks great!

Just trying to figure out how you did it.

First you are creating a MARKET_DATE in the main load before joining the actual list of market dates.

What is the + IterNo() - 1 doing?

Thanks in advance,

Olle

[Gysbert_Wassenaar]

See this blog post: Creating Reference Dates for Intervals

[#11]

Hi again Gysbert,

I'm fetching the exact same type of data from another source.

Hence i added an identical load (concatenate) underneath the one you helped me out with, however this ruined the intervals.

Do you know why?

Any suggestions how to arrange the second load?

Thanks in advance

//O

TRANSACTIONS:

LOAD [INSERTION DATE],
    TIME,
    [FLOW TYPE],
    AMOUNT,
    date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
    date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
    date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
    TEXT

FROM

(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

RIGHT JOIN
LOAD date(Date) as DATE
FROM

(ooxml, embedded labels, table is Sheet1);

CONCATENATE
LOAD [INSERTION DATE],
      TIME,
      TRADER,
        [FLOW TYPE],
        AMOUNT,
    date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
    date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
    Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
    TEXT

FROM

(ooxml, embedded labels, header is 1 lines, table is [HISTORICAL DATA])
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];