Excel formula not working in Talend

Highlighted
Eight Stars

Excel formula not working in Talend

Talend flow is given below

 

InputDBTable-->Excel-->tMap-->OutputDBTable (Input and output table is same)

 

Here, excel has formula in last 2 column's and i need to pull those results back to OutputDBTable. I have given the below formula in tMap but its showing me an error. Have anyone gone thru this scenario? Please suggest a solution to it.

 

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


Accepted Solutions
Eight Stars

Re: Excel formula not working in Talend

issue has been resolved by giving the formula in context variables in tMap.


All Replies
Ten Stars

Re: Excel formula not working in Talend

Unzip your xlsx, find your sheetname as .xml file, its pretty easy!

 

Eight Stars

Re: Excel formula not working in Talend

I didn't get that, can you please elaborate? Are you asking me to convert xlsx to xml and then use it in Talend?

Eight Stars

Re: Excel formula not working in Talend

issue has been resolved by giving the formula in context variables in tMap.

Ten Stars

Re: Excel formula not working in Talend

A .xlsx  file is a zip archive. No conversion is needed. just unarchive.
So if you unzip the file... there are multiple xml files which you could read with xml reading.

 

 

 

Eight Stars

Re: Excel formula not working in Talend

how to unarchive, please elaborate

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads