topic Re: Sum in Load Script in QlikView

Sum in Load Script

Fri, 25 Apr 2014 20:27:58 GMT

LOAD MdtId,

WJ,

DATE(Date#(WJ,'YYYYMMDD'),'DD.MM.YYYY') AS DATE,

Year(Date#(WJ,'YYYYMMDD')) AS Year,

InsVersion,

DelVersion,

Kontonummer,

WjMonat,

Bereichsnummer,

Buchungstyp,

Belegkategorie,

SummeSoll,

SummeHaben,

sum(SummeSoll) AS UmsatzSumme,

AnzahlKontobewegungen;

SQL SELECT *

the line in red is my own manual...

When i try to load the script i get the mistake

Fehler beim Lesen über OLEDB

SQL SELECT *

can help so?

Re: Sum in Load Script

its_anandrjs — Fri, 25 Apr 2014 20:32:59 GMT

You have to aggregate the table with all fields like

InsVersion,

DelVersion,

Kontonummer,

WjMonat,

Bereichsnummer,

Buchungstyp,

Belegkategorie,

SummeSoll,

SummeHaben,

Eg:-

Load

Dimension1,

Dimension2,

Dimension3,

Sum(Metrics) as Metrics

From Location

Group By

Dimension1,

Dimension2,

Dimension3;

I suggest do the SUM part in any resident table and then use your table.

Re: Sum in Load Script

rustyfishbones — Fri, 25 Apr 2014 20:42:41 GMT

You can only aggregate data from a table already loaded into Qlikview.

Load the table without the red part.

Then create a resident load from the loaded table, and you can add your expression in the resident load

Re: Sum in Load Script

ger_alegria — Fri, 25 Apr 2014 20:47:37 GMT

I guess you need group by the fields you don't sum. Also, you can try writing the specific table and do not use * in the load.

Re: Sum in Load Script

fernando_tonial — Fri, 25 Apr 2014 20:56:42 GMT

You try this:

Table_TMP:
LOAD MdtId,
    WJ,
    DATE(Date#(WJ,'YYYYMMDD'),'DD.MM.YYYY') AS DATE,
    Year(Date#(WJ,'YYYYMMDD')) AS Year,
    InsVersion,
    DelVersion,
    Kontonummer,
    WjMonat,
    Bereichsnummer,
    Buchungstyp,
    Belegkategorie,
    SummeSoll,
    SummeHaben,
    AnzahlKontobewegungen;
SQL SELECT * From Table;

Table:
LOAD 
    MdtId,
    WJ,
    DATE,
    Year,
    InsVersion,
    DelVersion,
    Kontonummer,
    WjMonat,
    Bereichsnummer,
    Buchungstyp,
    Belegkategorie,
    SummeSoll,
    SummeHaben,
    AnzahlKontobewegungen,
    sum(SummeSoll) AS UmsatzSumme
Resident Table_TMP
Group By
    MdtId,
    WJ,
    DATE,
    Year,
    InsVersion,
    DelVersion,
    Kontonummer,
    WjMonat,
    Bereichsnummer,
    Buchungstyp,
    Belegkategorie,
    SummeSoll,
    SummeHaben,
    AnzahlKontobewegungen;

Drop Table Table_TMP;

Kind Regards.

Tonial.

Re: Sum in Load Script

its_anandrjs — Fri, 25 Apr 2014 21:09:14 GMT

On the current loading table you cant do the SUM but for that you need to load the resident table and do the Sum aggregation on that table or may be read the QVD of the loaded table but easy way is

tmpTable:

LOAD

MdtId,

WJ,

DATE(Date#(WJ,'YYYYMMDD'),'DD.MM.YYYY') AS DATE,

Year(Date#(WJ,'YYYYMMDD')) AS Year,

InsVersion,

DelVersion,

Kontonummer,

WjMonat,

Bereichsnummer,

Buchungstyp,

Belegkategorie,

SummeSoll,

SummeHaben,

SummeSoll

AnzahlKontobewegungen;

SQL SELECT * Table;

FinalAggrTable:

LOAD

MdtId,

WJ,

DATE,

Year,

InsVersion,

DelVersion,

Kontonummer,

WjMonat,

Bereichsnummer,

Buchungstyp,

Belegkategorie,

SummeSoll,

SummeHaben,

AnzahlKontobewegungen,

Sum(SummeSoll) AS UmsatzSumme;

Resident tmpTable

Group By

MdtId,

WJ,

DATE,

Year,

InsVersion,

DelVersion,

Kontonummer,

WjMonat,

Bereichsnummer,

Buchungstyp,

Belegkategorie,

SummeSoll,

SummeHaben,

AnzahlKontobewegungen;

Drop Table tmpTable;