topic Re: Problem with group by in loadscript in QlikView

Problem with group by in loadscript

t_hylander — Mon, 18 Jun 2018 13:45:32 GMT

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

Re: Problem with group by in loadscript

fvelascog72 — Mon, 18 Jun 2018 13:49:50 GMT

Hi,

Try with:

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

;

Saludos

Re: Problem with group by in loadscript

Anil_Babu_Samineni — Mon, 18 Jun 2018 13:52:31 GMT

Try this?

Load *

where (Status=2 and

Leverantor = '90101' and

Artikel >= '1000' or Artikel <= '2999' and

year(date(Transtid)) = '2018') Group By KPI_månad;

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); //Group By allows only for Aggregate functions...

Re: Problem with group by in loadscript

sunny_talwar — Mon, 18 Jun 2018 13:53:26 GMT

Try this

Table:

LOAD *,

Sum(Nettovikt) as TotalTon_Månad

Where Status=2 and Leverantor = '90101' and (Artikel >= '1000' or Artikel <= '2999') and Year = '2018'

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

LOAD '2018' as KPI_år,

Month(Transtid) as KPI_månad,

Year(Transtid) as Year,

'1' as _KPI_Intransp,

Nettovikt

FROM [..\1_Extract\Vågen\TRANS.qvd] (qvd);

Re: Problem with group by in loadscript

t_hylander — Mon, 18 Jun 2018 13:55:23 GMT

Works, but i get wierd numbers, looks like it ignores the where-statement...

Re: Problem with group by in loadscript

vishsaggi — Mon, 18 Jun 2018 14:26:42 GMT

Another way may be:

LOAD *,

sum(Nettovikt) as TotalTon_Månad

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

Load

'2018' as KPI_år,

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

'1' as _KPI_Intransp,

Nettovikt

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'

;

Re: Problem with group by in loadscript

t_hylander — Tue, 19 Jun 2018 09:30:22 GMT

Just getting "Invalid expression"

Re: Problem with group by in loadscript

t_hylander — Tue, 19 Jun 2018 09:30:28 GMT

Just getting "Invalid expression"

Re: Problem with group by in loadscript

t_hylander — Tue, 19 Jun 2018 09:46:24 GMT

Made a exemple you can try with if youd like.

Re: Problem with group by in loadscript

sunny_talwar — Tue, 19 Jun 2018 11:16:28 GMT

It was my bad all the way through... 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", 2110

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

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

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

];

FinalTable:

LOAD KPI_år,

KPI_månad,

_KPI_Intransp,

Sum(Nettovikt) as TotalTon_Månad

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

LOAD '2018' as KPI_år,

Month(Date(Transtid)) as KPI_månad,

'1' as _KPI_Intransp,

Nettovikt

Resident Vagen

where Status = 2 and

Leverantor = '90101' and

(Artikel >= '1000' or Artikel <= '2999') and

Year(Date(Transtid)) = '2018';

DROP Table Vagen;

Re: Problem with group by in loadscript

Peter_Cammaert — Tue, 19 Jun 2018 11:17:49 GMT

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.

Re: Problem with group by in loadscript

t_hylander — Thu, 21 Jun 2018 09:47:19 GMT

yeah I noticed that, now It all works!

Re: Problem with group by in loadscript

t_hylander — Thu, 21 Jun 2018 10:49:03 GMT

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

Re: Problem with group by in loadscript

sunny_talwar — Thu, 21 Jun 2018 11:09:18 GMT

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

Re: Problem with group by in loadscript

t_hylander — Thu, 21 Jun 2018 11:11:37 GMT

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

Re: Problem with group by in loadscript

sunny_talwar — Thu, 21 Jun 2018 11:18:29 GMT

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

Re: Problem with group by in loadscript

t_hylander — Thu, 21 Jun 2018 11:23:19 GMT

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

Re: Problem with group by in loadscript

sunny_talwar — Thu, 21 Jun 2018 11:26:29 GMT

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

;

Re: Problem with group by in loadscript

t_hylander — Thu, 21 Jun 2018 11:39:17 GMT

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.

Re: Problem with group by in loadscript

sunny_talwar — Thu, 21 Jun 2018 11:43:30 GMT

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?