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

[fvelascog72]

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

[Anil_Babu_Samineni]

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

[sunny_talwar]

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

[t_hylander]

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

[vishsaggi]

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'

;

[t_hylander]

Just getting "Invalid expression"

[t_hylander]

Just getting "Invalid expression"

[t_hylander]

Made a exemple you can try with if youd like.

[sunny_talwar]

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;