Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi,
what is the comparison rule ? always compare the lastest "3. Datum Faktura" value with the lastest "2. Datum Faktura" value ?
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;
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.
is this what you need ?
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];
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.
My mistake, I was reading it 12.02.2018..
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;
Hello Youssef,
this worked. Thank you!
Greets,
GA