invalid expression

[#1]

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

[sunny_talwar]

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.

[maxgro]

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

[#4]

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

[sunny_talwar]

Can you share the error you are getting?

[#6]

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

[Anonymous]

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

[maxgro]

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

[sasiparupudi1]

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

1) 

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

2)

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

[sunny_talwar]

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