topic Re: SUMIFS in LOAD script in QlikView

SUMIFS in LOAD script

Tue, 12 May 2015 08:02:50 GMT

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

Re: SUMIFS in LOAD script

Gysbert_Wassenaar — Tue, 12 May 2015 08:08:20 GMT

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;

Re: SUMIFS in LOAD script

Tue, 12 May 2015 08:35:35 GMT

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

Re: SUMIFS in LOAD script

Tue, 12 May 2015 08:51:21 GMT

Thanks Gysbert.

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

Kind Regards,

Olle

Re: SUMIFS in LOAD script

jagan — Tue, 12 May 2015 08:57:55 GMT

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.

Re: SUMIFS in LOAD script

Tue, 12 May 2015 09:13:05 GMT

Hi Jagan,

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

//O

Re: SUMIFS in LOAD script

jagan — Tue, 12 May 2015 09:27:08 GMT

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.

Re: SUMIFS in LOAD script

Tue, 12 May 2015 12:22:09 GMT

Hi again Gysbert,

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

Any clues how to structure the group by?

Re: SUMIFS in LOAD script

Gysbert_Wassenaar — Tue, 12 May 2015 12:24:12 GMT

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.

Re: SUMIFS in LOAD script

Wed, 13 May 2015 11:01:54 GMT

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

Re: SUMIFS in LOAD script

stabben23 — Wed, 13 May 2015 11:10:41 GMT

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

Re: SUMIFS in LOAD script

Wed, 13 May 2015 11:17:03 GMT

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

Re: SUMIFS in LOAD script

stabben23 — Wed, 13 May 2015 11:43:57 GMT

Not Amount

Re: SUMIFS in LOAD script

swuehl — Wed, 13 May 2015 11:44:59 GMT

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?

Re: SUMIFS in LOAD script

jagan — Thu, 14 May 2015 02:13:23 GMT

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];

Re: SUMIFS in LOAD script

Fri, 15 May 2015 09:25:58 GMT

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

Re: SUMIFS in LOAD script

Gysbert_Wassenaar — Fri, 15 May 2015 09:50:57 GMT

See attached qvw.

Re: SUMIFS in LOAD script

Fri, 15 May 2015 11:38:52 GMT

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

Re: SUMIFS in LOAD script

Gysbert_Wassenaar — Fri, 15 May 2015 11:43:38 GMT

See this blog post: Creating Reference Dates for Intervals

Re: SUMIFS in LOAD script

Fri, 15 May 2015 12:57:12 GMT

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];