Search code examples
informaticainformatica-powercenterinformatica-cloudinformatica-powerexchange

How can I write the expression such that

I have this expression

(IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,2,13)))='',NULL,LTRIM(RTRIM(SUBSTR(HEADER_INFO,2,13)))))

for the sample data below, but it is failing when it finds more than one D records after the H record.

My goal is to have an expression that if it sees multiple D records after the H record, it reads those records as well, and if only one D record, it reads that record and move to the next H record.

Right now, it is failing because it is expecting an H record after each D record, but there are situations as shown below where there are more than one D records after an H record.

HP5919274001273HWA8365C    DEFORD              DONITA         1245327121     1184950479                    6021928     0 20191001201910017        0       
D1   D00054317763400       
HP2519274011110HWA8365C    DEFORD              DONITA         1245327121     1184950479                    6021928     0 20191001201910017        0       
D1   P00054317763400       
HQ2519280005752HWA8365C    DEFORD              DONITA         1245327121     1184950479                    6022011     0 201910072019100710       0       
D1   P49348004537100       
D2   P49348015339100       
D3   P00054350049100       
HQ2519289004679HWA8365C    DEFORD              DONITA         1245327121     1184950479                    6022011     1 201910072019101610       0       
D1   P49348004537100       
D2   P49348015339100       
D3   P00054350049100       
HP2519309005224HWA8365C    DEFORD              DONITA         1245327121     1184950479                    2012699     0 201911052019110514       0       
D1   D00904667840315       
HP2519309005132HWA8365C    DEFORD              DONITA         1245327121     1184950479                    2012699     0 201911052019110514       0

Yes attached please

sample_output

Solution

  • I resolved the issue by creating variables for all the fields, so my conditions are like below (IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,2,13)))='',v_v_ICN,LTRIM(RTRIM(SUBSTR(HEADER_INFO,2,13))))) (IIF(LTRIM(RTRIM(SUBSTR(HEADER_INFO,15,12)))='',v_v_MBR_ID,LTRIM(RTRIM(SUBSTR(HEADER_INFO,15,12)))))