Calculate Date1 > Date2

[gino2780]

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

[YoussefBelloum]

Hi,

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

[consultant_bi]

Hello

try this

Table:

load * inline

[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:

load

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

resident Table;

drop table Table;

[gino2780]

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.

[YoussefBelloum]

is this what you need ?

[gino2780]

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

This is the script:

Faktura:

LOAD

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

LOAD [ID],

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

[gino2780]

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.

[YoussefBelloum]

My mistake, I was reading it 12.02.2018..

[YoussefBelloum · Accepted solution]

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;

[gino2780]

Hello Youssef,

this worked. Thank you!

Greets,

GA