Calculate due date based on payment terms

[ulfmattsson]

Hi,

I'm trying to calculate due date for invoices based on payment terms. We have 68 different payment terms with different formulas for each. As suggested in an older thread

https://community.qlik.com/message/873768#873768

I have managed to store each formula as a variable with the same name as the payment term in my data file. However I can't match the variable with my data field. Could someone please help with the syntax or other suggestion?

Invoices:
LOAD

  %InvID,
[Document Date], 
PTCode,
Date([Document Date]+1) as duda, //Example formula

  $(PT01) as [Due Date 1], //Fixed variable works
Evaluate('$(' & PTCode & ')') as [Due Date 2] //Getting correct variable from PTCode field does not work
FROM
Data source

  Thanks

[ulfmattsson]

Hi Imad,

I tried your solution but the fact that my formulas use document date seems to complicate matters.

I did a resident load to evalate the correct formula string from the first load but again the field is empty.

Invoices:
LOAD %InvID,
[Document Date], 
PTCode,
Evaluate(PTCode) as [Due Date2]
FROM
Invoice data.xlsx
(ooxml, embedded labels, table is Sheet1);


Invoices2:
Load
%InvID as Inv,
[Document Date] as docdate, 
PTCode as pt,
[Due Date2] as duedatestring,
Evaluate([Due Date2]) as [Due Date]
Resident Invoices;

Drop Table Invoices;

[ulfmattsson · Accepted solution]

Hi

If anyone has the same problem as I did I finally found a solution through this old thread:

Inside field calculation?

Variables:
Load * Inline [
PTCode, Formula
PT01, Date('DocDate'+1)
PT02, Date('DocDate'+2)
PT03, Date('DocDate'+3)
PT04, Date('DocDate'+4)
PT05, Date('DocDate'+5)
];

For vRowNo = 0 to NoOfRows('Variables')-1
Let vVariableName = Peek('PTCode',vRowNo,'Variables');
Let $(vVariableName) = Peek('Formula',vRowNo,'Variables');
Next vRowNo

Drop table Variables;

Data:
LOAD %InvID as ID,
[Document Date] as DocDate,

Evaluate(PTCode) as Calc,  
PTCode  
FROM
Invoice data.xlsx
(ooxml, embedded labels, table is Sheet1);

LET vCalcs = repeat('replace(',nooffields('Data')) & 'Calc';

FOR I = 1 TO nooffields('Data')
LET vCalcs = vCalcs & ',' & chr(39) & fieldname(I,'Data') & chr(39) & ',' & fieldname(I,'Data') & ')';
NEXT

LEFT JOIN (Data)
LOAD
ID
,$(vCalcs) as VarForm
,evaluate($(vCalcs)) as Result
RESIDENT Data
;