Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a date field SUMMARY_DATE (31/05/2018) and for simplicity in coding, I would like to create another field where I can mark the latest date as zero and the prior as:
SUMMARY_DATE RELATIVE_MONTH
31/05/2018 0
30/04/2018 -1
31/03/2018 -2
28/02/2018 -3
31/01/2018 -4
Ideally within my Script.
Thanks for all the help,
Aksel
This might be another way...
Table:
LOAD * INLINE [
SUMMARY_DATE
31/05/2018
30/04/2018
31/03/2018
28/02/2018
31/01/2018
];
FinalTable:
LOAD SUMMARY_DATE,
RangeSum(Peek('RELATIVE_MONTH'), (Year(SUMMARY_DATE)*12 + Month(SUMMARY_DATE))-(Year(Previous(SUMMARY_DATE))*12 + Month(Previous(SUMMARY_DATE)))) as RELATIVE_MONTH
Resident Table;
DROP Table Table;
hello
why not :
loading your date in a table
loading a 2nd table from the previous resident on order by date descending
computing a field with -1*rowno()+1
This might be another way...
Table:
LOAD * INLINE [
SUMMARY_DATE
31/05/2018
30/04/2018
31/03/2018
28/02/2018
31/01/2018
];
FinalTable:
LOAD SUMMARY_DATE,
RangeSum(Peek('RELATIVE_MONTH'), (Year(SUMMARY_DATE)*12 + Month(SUMMARY_DATE))-(Year(Previous(SUMMARY_DATE))*12 + Month(Previous(SUMMARY_DATE)))) as RELATIVE_MONTH
Resident Table;
DROP Table Table;
Thank you very much.