topic Re: invalid expression in QlikView

invalid expression

Sun, 24 Jan 2016 13:45:01 GMT

Hi,

I am a beginner an i have a Problem with this Expression:

Left join(Provisionstabelle)

Load %Konto as Konto,

%Datum,

only(Month(%Datum) & '/' & year(%Datum)) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where %Datum >= 01.12.2015 and %Datum <= 31.12.2015

and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

Can somebody explain, what is wrong with this Expression.

Best regards,

Florian

Re: invalid expression

sunny_talwar — Sun, 24 Jan 2016 16:09:26 GMT

I think the issue is that you are using Only() function which is considered as a aggregate function and needs a group by statement at the end. I am not really sure why you need a only here. Can you try this without Only?

Left join(Provisionstabelle)

Load %Konto as Konto,

%Datum,

Month(%Datum) & '/' & year(%Datum) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where %Datum >= '01.12.2015' and %Datum <= '31.12.2015'

and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

UPDATE: and as maxgro‌ said add single quotes around the date values.

Re: invalid expression

maxgro — Sun, 24 Jan 2016 16:33:40 GMT

remove only, add quotes to date (bold)

Left join(Provisionstabelle)

Load %Konto as Konto,

%Datum,

Month(%Datum) & '/' & year(%Datum) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where %Datum >= '01.12.2015' and %Datum <= '31.12.2015' and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

Re: invalid expression

Sun, 24 Jan 2016 19:51:52 GMT

Thank you for your anwer, but the expression ist allways invalid.

Re: invalid expression

sunny_talwar — Sun, 24 Jan 2016 20:14:10 GMT

Can you share the error you are getting?

Re: invalid expression

Mon, 25 Jan 2016 07:26:03 GMT

I found the error. Your advice respective the date was right. I Change the format of the date and the program is runing. thank you very much.

Best Regards,

Florian

Re: invalid expression

Anonymous — Mon, 25 Jan 2016 07:32:59 GMT

where is this Flag %IsErtrag??

may be like this?

Load %Konto as Konto,

%Datum,

Month(%Datum) & '/' & year(%Datum) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where Date(%Datum,'DD.MM.YYYY') >= 01.12.2015 and Date(%Datum,'DD.MM.YYYY') <= 31.12.2015

and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

Re: invalid expression

maxgro — Mon, 25 Jan 2016 08:18:08 GMT

I tried in a test script the script I posted above (bold), I didn't get any error (see tha image).

Could you post your script and your error?

SET DateFormat='DD.MM.YYYY';

Provisionstabelle:

LOAD 1 as a AutoGenerate 1;

Umsatz:

load 1 as %Konto, makedate(2015,12,1) as %Datum, 1 as Ertrag, 'Darlehenszinsen' as ErtragsArt, 1 as %IsErtrag

AutoGenerate 1;

Left join(Provisionstabelle)

Load %Konto as Konto,

%Datum,

Month(%Datum) & '/' & year(%Datum) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where %Datum >= '01.12.2015' and %Datum <= '31.12.2015'

and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

Re: invalid expression

sasiparupudi1 — Mon, 25 Jan 2016 09:10:40 GMT

With Dates you got be careful that you are comparing apples to apples.. Please try like below

If your %Datum is a date field with DD.MM.YYYY format then

Left join(Provisionstabelle)

Load %Konto as Konto,

%Datum,

Month(%Datum) & '/' & year(%Datum) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where %Datum >= Date(Date#('01.12.2015','DD.MM.YYYY'),'DD.MM.YYYY') and %Datum <= Date(Date#('31.12.2015','DD.MM.YYYY'),,'DD.MM.YYYY')

and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

if Your %Datum is a string with DD.MM.YYYY format then

Left join(Provisionstabelle)

Load %Konto as Konto,

%Datum,

Month(Date#(%Datum,'DD.MM.YYYY')) & '/' & Year(Date#(%Datum,'DD.MM.YYYY')) as Provisionsmonat,

Ertrag,

ErtragsArt

Resident Umsatz

where %Datum >= '01.12.2015' and %Datum <= '31.12.2015'

and %IsErtrag = 1

and Match(ErtragsArt,'Buchungsposten Gebühren','Darlehenszinsen','Ertrag aus Rücklastschriften','Grundgebühren','Portogebühren','Sollzinsen','sonstiges','Überziehungszinsen');

Hope this helps

Re: invalid expression

sunny_talwar — Mon, 25 Jan 2016 10:54:40 GMT

Florian Berner wrote:

I found the error. Your advice respective the date was right. I Change the format of the date and the program is runing. thank you very much.

Best Regards,

Florian

I think you are referring to maxgro‌ here. I would recommend closing this thread down by marking correct and/or any useful answers.

Best,

Sunny