Seven Stars

Excel formula not working from talend

Below is the formula written in excel. I have hard-coded this formula in tMap to output the value in to the column for each row but its not working. Any suggestions please.

 

=IF(ISBLANK(B5),"",IF(OR(AND(ISBLANK(M5),ISBLANK(I5)),ISNA(VLOOKUP(B5,Demographics!C:F,4,FALSE))),0,IF(AND(OR(C5="Yes",ISTEXT(VLOOKUP(G5,CancerDx,1,FALSE))),ISNUMBER(D5),D5<=MPend,OR(AND(OR(H5="Yes",ISTEXT(VLOOKUP(K5,RadEnc,1,FALSE))),I5>=MPstart,I5<=MPend),AND(OR(L5="Yes",AND(ISTEXT(VLOOKUP(O5,PainEnc,1,FALSE)),ISTEXT(VLOOKUP(R5,Chemo,1,FALSE)),ISTEXT(VLOOKUP(U5,Chemo,1,FALSE)))),M5>=MPstart,M5<=MPend,IF(ISERR(DATE(YEAR(M5),MONTH(M5),DAY(M5)-30)),,P5>=DATE(YEAR(M5),MONTH(M5),DAY(M5)-30)),P5<=M5,S5<=DATE(YEAR(M5),MONTH(M5),DAY(M5)+30),S5>=M5))),1,0)))

1 ACCEPTED SOLUTION

Accepted Solutions
Fifteen Stars

Re: Excel formula not working from talend

As I said before, you cannot do this with the Talend component, but you can using @jlolling's component 

(http://jan-lolling.de/talend/components/help/tFileExcelSheetOutput.pdf). You need to goto Talend Exchange to acquire this component (https://exchange.talend.com/).

Rilhia Solutions
4 REPLIES
Twelve Stars

Re: Excel formula not working from talend

@samisyed80,you can not directly use the same excel formula in tMap. it will not work.

Manohar B
Seven Stars

Re: Excel formula not working from talend

i tried with normal transmission to load the data in a pre-generated excel, i succeeded but the formula didn't get calculated. That's why i tried this way. Any other solution you have?

Fifteen Stars

Re: Excel formula not working from talend

As I said before, you cannot do this with the Talend component, but you can using @jlolling's component 

(http://jan-lolling.de/talend/components/help/tFileExcelSheetOutput.pdf). You need to goto Talend Exchange to acquire this component (https://exchange.talend.com/).

Rilhia Solutions
Seven Stars

Re: Excel formula not working from talend

OK rHall