topic Excel formula not working from talend in Talend Studio

Excel formula not working from talend

Anonymous — Fri, 11 May 2018 06:29:42 GMT

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)))

Re: Excel formula not working from talend

manodwhb — Fri, 11 May 2018 06:46:05 GMT

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

Re: Excel formula not working from talend

Anonymous — Fri, 11 May 2018 07:06:27 GMT

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?

Re: Excel formula not working from talend

Anonymous — Fri, 11 May 2018 09:55:59 GMT

As I said before, you cannot do this with the Talend component, but you can using @lli'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/).

Re: Excel formula not working from talend

Anonymous — Fri, 11 May 2018 10:25:08 GMT

OK rHall