Using Formulas in Edit Script

[#1]

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?

[Nicole-Smith]

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

];

[Anonymous]

John

Use a preceding load including your formula above the inline load.

Best Regards,     Bill

[#4 · Accepted solution]

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;