Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
t_hylander
Creator
Creator

Problem with group by in loadscript

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

1 Solution

Accepted Solutions
t_hylander
Creator
Creator
Author

I made a workaround;

load
KPI_år,
KPI_månad,
sum(KPI_Nettovikt) as KPI_TotalTon
where _KPI_Intransp = '1'
group by KPI_år, KPI_månad;


Load
'2018' 
as KPI_år,
month(date(Transtid)) as KPI_månad,
Nettovikt as KPI_Nettovikt,
if(Artikel>='1000',
if(Artikel<='2999', '1','0')) as _KPI_Intransp
from [..\1_Extract\Vågen\TRANS.qvd] (qvd)
WHERE(
Status = '2' and
Kund = '90101' and
year(date(Transtid)) = '2018')
;

View solution in original post

21 Replies
fvelascog72
Partner
Partner

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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
Creator
Creator
Author

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

vishsaggi
Champion III
Champion III

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
Creator
Creator
Author

Just getting "Invalid expression"

t_hylander
Creator
Creator
Author

Just getting "Invalid expression"

t_hylander
Creator
Creator
Author

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;