How can I write an IFF function in Informatica for the attached sample data. I have a target table which consist of different columns. I currently have different expressions like. IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,1,1)))='',NULL,LTRIM(RTRIM(SUBSTR(HEADER_INFO,1,1))))
IIF(LTRIM(RTRIM(SUBSTR(DETAIL_INFO,2,4)))='',NULL,LTRIM(RTRIM(SUBSTR(DETAIL_INFO,2,4))))
The first expression above picks up H from the first row below and assigns it to a column A, there is another expression function which assigns P to another column B, it continues until it reaches the last character on the first row and assigns it to a column P. The second IFF function above reads 1 and assign it to a column W, then similar expression with different position assings P to a certain column X,another assins 009046678402 to column Y, then another assigns 40 to Z. What i want to achieve is that, after reading and assigning values on the second row, if the next row after that starts with a 'D' again, it should still read it. But the current IFF function I have, only reads if there is rows alternate as HP... D1... where as I want that if there is a scenario as shown in the attachment where you have HP... D1... D2... D3... it should still read the and assign values ofenter image description here D1..., D2..., D3...
HP2519263013900HWA8365C DEFORD DONITA 1245327121 1184950479 2012516 0 201909202019092016 0 D1 P00904667840240
I resolved the issue by creating variable ports for each field and replacing the null in my TRIM with their respective variable ports like below IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,1,1)))='',v_ID,LTRIM(RTRIM(SUBSTR(HEADER_INFO,1,1)))), IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,2,4)))='',v_LST_Name,LTRIM(RTRIM(SUBSTR(HEADER_INFO,2,4))))
