Sum in Load Script

[#1]

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?

[its_anandrjs]

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.

[rustyfishbones]

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

[ger_alegria]

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.

[fernando_tonial · Accepted solution]

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.

[its_anandrjs]

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;