Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to use formulas in an Inline Load Statement in the Edit Script? I am loading in Payment Terms and Days to Pay so when there is a set number of days to pay it is easy such as:
Left Join
LOAD * INLINE [
Payment Term, Days to Pay
XXXX, 0
XXXY, 15
XXYY, 30
];
But sometimes the payment term defines the days to pay as "the 15th of the next month." In a formula this would be:
=if([Payment Terms]='0006',MonthEnd(AddMonths([Invoice Date],1)+15)
However this does not work in the script. Any ideas on how to translate this so that it would work in the script?
Hello,
Maybe you can try using a Resident table and create your calculated field, something like this:
TABLE1_TMP:
LOAD * INLINE [
Payment Term, Days to Pay
XXXX, 0
XXXY, 15
XXYY, 30
];
LEFT JOIN (TABLE X)
LOAD *,
if([Payment Terms]='0006',MonthEnd(AddMonths([Invoice Date],1)+15) AS MYFORMULA
Resident TABLE1_TMP;
DROP Table TABLE1_TMP;
Left Join (YourTable)
LOAD [Payment Term],
if([Payment Term]='0006',MonthEnd(AddMonths([Invoice Date],1)+15), [Days to Pay]) as [Days to Pay]
INLINE [
Payment Term, Days to Pay
XXXX, 0
XXXY, 15
XXYY, 30
];
John
Use a preceding load including your formula above the inline load.
Best Regards, Bill
Hello,
Maybe you can try using a Resident table and create your calculated field, something like this:
TABLE1_TMP:
LOAD * INLINE [
Payment Term, Days to Pay
XXXX, 0
XXXY, 15
XXYY, 30
];
LEFT JOIN (TABLE X)
LOAD *,
if([Payment Terms]='0006',MonthEnd(AddMonths([Invoice Date],1)+15) AS MYFORMULA
Resident TABLE1_TMP;
DROP Table TABLE1_TMP;