10 Replies Latest reply: Jul 19, 2018 10:13 AM by Youssef Belloum

# Calculate Date1 > Date2

Hello dear community members,

howe can i generate a new dimension or flag in the script that can show me something like this:

Show me when "2. Datum Faktura"  (01.02.2017) is older than "3. Datum Faktura" (08.02.2017)

Example data:

 ID 1. Datum Faktura 2. Datum Faktura 3. Datum Faktura Sum(Fakturabetrag) 95 08.02.2017 - - 3.921,50 € 95 05.01.2018 - - 1.602,00 € 95 12.01.2018 - - 1.602,00 € 95 - 01.02.2017 - 3.921,50 € 95 - - 08.02.2017 -3.921,50 € 95 - - 12.01.2018 -1.602,00 €

Greets

GA

• ###### Re: Calculate Date1 > Date2 or

Hi,

what is the comparison rule ? always compare the lastest "3. Datum Faktura" value with the lastest "2. Datum Faktura" value ?

• ###### Re: Calculate Date1 > Date2 or

Good question, i forgot to point that out.

Actually "2. Datum Faktura" is the final invoice.

"3. Datum Faktura" is a correction invoice.

So when 3. comes in the timeline after 2. i have to consider it as not yet finally invoiced, that's the main purpose of the flag.

• ###### Re: Calculate Date1 > Date2 or

is this what you need ?

• ###### Re: Calculate Date1 > Date2 or

Yes, Yousseff. Although the second "3. Datum Faktura" (12.01.2017) should not be considered in the flag, because it happened before 01.02.2017.

• ###### Re: Calculate Date1 > Date2 or

My mistake, I was reading it 12.02.2018..

• ###### Re: Calculate Date1 > Date2 or

make sure to evaluate and format your date fields correctly and try this as a second reload on your table:

FINAL_TABLE

LOAD *,if(Peek([2. Datum Faktura])< [3. Datum Faktura],'NOT YET FACTURED') as FLAG

Resident YOUR_TABLE;

• ###### Re: Calculate Date1 > Date2

Hello Youssef,

this worked. Thank you!

Greets,

GA

• ###### Re: Calculate Date1 > Date2

You're welcome Good luck

• ###### Re: Calculate Date1 > Date2 or

Hello

try this

Table:

[ID,DATUM_1,DATUM_2,DATUM_3

1,23/01/2010,23/01/2011,23/01/2012

2,23/01/2010,23/01/2011,22/01/2011

3,23/01/2010,23/01/2011,23/01/2015

];

TABLE2:

*,if (DATUM_2>DATUM_3,1,0) as Flag

resident Table;

drop table Table;

• ###### Re: Calculate Date1 > Date2 or

I've already tried this, unfortunately i get a "invalid expression"-error after data load:

This is the script:

Faktura:

"PipelineID" as [ID],

"Pipeline ID:Kostenvoranschlag",

Fakturabetrag,

"Datum Faktura",

Erstellt_Faktura,

"Erstellt von_Faktura",

Geändert_Faktura,

"Geändert von_Faktura",

"SAP Belegnummer",

Fakturaart,

"1. Faktura",

"2. Faktura",

"3. Faktura",

if([1. Faktura]= '1. Zwischenfaktura' and [Datum Faktura]<>'', [Datum Faktura]) as [1. Datum Faktura],

if([2. Faktura]= '2. Endfaktura' and [Datum Faktura]<>'', [Datum Faktura]) as [2. Datum Faktura],

if([3. Faktura]= '3. Korrektur' and [Datum Faktura]<>'', [Datum Faktura]) as [3. Datum Faktura],

__KEY_data_Faktura

FROM [lib://K Netzlaufwerk BD/Export\Faktura.qvd]

(qvd);

Left Join (Faktura)

If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([2. Faktura]) = '2. Endfaktura', 1, 0) as endfakturiert,

If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([2. Faktura]) = '2. Endfaktura' and MaxString([3. Faktura]) = '3. Korrektur', 1, 0) as "korrigiert fakturiert",

If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([3. Faktura]) = '3. Korrektur', 1, 0) as "korrigiert zwischenfakturiert",

If(MaxString([2. Faktura]) <> '2. Endfaktura', 1, 0) as "nf",

If ([2. Datum Faktura]>[3. Datum Faktura], 1, 0) as flag  //--> this line results in the "invalid expression" error //

Resident Faktura

Group By [ID];