Problem with group by in loadscript

[t_hylander]

Hi,

I've been staring at this for a while now but can't see the problem, but Qlikview is kindly whining about "Error in expression:

')' expected" somewhere.

I'd like extract data from a qvd and sum it up grouped by year and month. I tried to make it easier for me just using 2018, therefore only using month in the group by.

The script looks like this;

Load
'2018' as KPI_år,
month(date(Transtid)) as KPI_månad,
'1' as _KPI_Intransp,
sum(Nettovikt) as TotalTon_Månad
from [..\1_Extract\Vågen\TRANS.qvd] (qvd)
where (
Status=2 and
Leverantor = '90101' and
Artikel >= '1000' or Artikel <= '2999' and
year(date(Transtid)) = '2018'
Group by month(date(Transtid))
);

[Peter_Cammaert]

Replace the asterisk in the top-level LOAD with the list of three field names specified in the GROUP BY clause. That way you'll avoid field Nettovikt being included both in the Sum() call and on its own as well (through the wildcard spec). The latter is not possible.

See attachment.

[t_hylander]

yeah I noticed that, now It all works!

[t_hylander]

I was too Quick there...it stil ignores Artikel selection (I was too lazy to change in the example).

If I change Artikel in the above example to 3100 on one month, it still shows

[sunny_talwar]

Can you know add it to show what the issue is?

[t_hylander]

Sure, fiddle lite with it but Think its the same still...

[sunny_talwar]

What exactly is the problem? can you highlight the issue from the qvw and explain what you would like to see?

[t_hylander]

The left part is the faulty one, where I try to sum on load, and the right side is the correct one, where I loaded all transactions and made the sum in a straight table (and selected only 2110)

Even if I have a intervall in the loadscript that exclude Artikel 3110 for April it still shows.

Hope it explains it better

[sunny_talwar]

A script based aggregation is usually static and doesn't work with selections... but may be try this

Vagen:

LOAD * INLINE [

    Transtid, Status, Leverantor, Nettovikt, Artikel

    2018-01-15, 2, 90101, "12,00", 2110

    2018-01-17, 2, 90101, "12,58", 2110

    2018-01-21, 2, 90101, "16,58", 2110

    2018-01-22, 2, 90101, "12,58", 2110

    2018-02-15, 2, 90101, "22,58", 2110

    2018-02-17, 2, 90101, "12,58", 2110

    2018-02-19, 2, 90101, "42,58", 2110

    2018-03-11, 2, 90101, "12,58", 2110

    2018-03-15, 2, 90101, "12,00", 2110

    2018-03-17, 2, 90101, "12,58", 2110

    2018-04-11, 2, 90101, "15,34", 3110

    2018-04-15, 2, 90101, "12,58", 3110

    2018-04-17, 2, 90101, "22,58", 3110

    2018-05-11, 2, 90101, "22,33", 2110

    2018-05-15, 2, 90101, "12,58", 2110

    2018-05-17, 2, 90101, "15,55", 2110

    2018-06-11, 2, 90101, "12,58", 2110

    2018-06-15, 2, 90101, "13,77", 2110

    2018-06-17, 2, 90101, "12,58", 2110

];

LOAD KPI_år, KPI_månad, _KPI_Intransp, Artikel,

    sum(Nettovikt) as TotalTon_Månad

Group By KPI_år, KPI_månad, _KPI_Intransp, Artikel;

Load

'2018'                as KPI_år,

month(date(Transtid)) as KPI_månad,

'1'                  as _KPI_Intransp,

Nettovikt,

Artikel

Resident Vagen

WHERE(

(Artikel >= '1000' Or

Artikel <= '2999') And

Status = '2' and

    Leverantor = '90101' and

    year(date(Transtid)) = '2018')

;

[t_hylander]

I just want the sum for all articles per month, now Im getting per article as well..

The real data includes perhaps 100 diffrent articles...

but unless Qlikview cant handle OR-statements I guess I have to load everything and do the calculations inside.

It does work in the example qvd since you didnt change name on the article, so if I erase all selections it still shows.

[sunny_talwar]

I guess I am not sure I understand your expectation from the aggregation in the script. Aggregation in the script cannot be dynamic which can work well with selections. It creates a static value which can only change based on the fields used in the group by... any information more granular can no longer be used. This may not answer the problem you are seeing, but I just wanted to highlight this.

With regards the problem you are facing, I don't think I completely understand what you are running into. Would you be able to expand with details?