Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I'm trying to calculate the last invoice date based on a version date (snapshot date).
In order to accomplish this I have loaded both the snapshot dates as well as the detailed inventory movements as seperate tables.
The next step is to calculate the last invoice date at each snapshot date and this is where i got stuck.
I created a cartesian product of the version dates and all movements.
In a next step I'm trying to calculate the last invoice date given the version date as follows:
Rotation:
load VersionDate,
Material,
Warehouse,
if(Date#(InvoiceDate) <= Date#(VersionDate), max(IssueDate),'Unknown') as LastInvoiceDate
Resident VersionDate
group by VersionDate, SSP, Material;
The result is en 'Error in expression'. Can anyone what is wrong in this statement?
Thank you very much in advance,
Kr,
K
ps: The difficulty here is that the last invoice date for a certain material at a certain warehouse could be several months earlier than the month of the snapshot but then that still remains the last invoice date.
Try this:
Rotation:
load
max(IssueDate) as LastInvoiceDate
VersionDate
Material,
SSP
Resident VersionDate
where InvoiceDate <= VersionDate
group by VersionDate, SSP, Material;
HTH
Sushil
Try this:
Rotation:
load
max(IssueDate) as LastInvoiceDate
VersionDate
Material,
SSP
Resident VersionDate
where InvoiceDate <= VersionDate
group by VersionDate, SSP, Material;
HTH
Sushil
Try including SSP in the load like:
Rotation:
load VersionDate,
SSP,
Material,
Warehouse,
if(Date#(InvoiceDate) <= Date#(VersionDate), max(IssueDate),'Unknown') as LastInvoiceDate
Resident VersionDate
group by VersionDate, SSP, Material;