topic Re: Using Formulas in Edit Script in QlikView

Using Formulas in Edit Script

Wed, 11 Dec 2013 20:33:16 GMT

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?

Re: Using Formulas in Edit Script

Nicole-Smith — Wed, 11 Dec 2013 20:37:51 GMT

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

];

Re: Using Formulas in Edit Script

Anonymous — Wed, 11 Dec 2013 20:38:35 GMT

John

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

Best Regards, Bill

Re: Using Formulas in Edit Script

Wed, 11 Dec 2013 20:41:09 GMT

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;