Search code examples
sqldatabasemonitoringinformatica-powercenter

Split String into multiple columns in Informatica powercenter

my goal is to take querypaths from an excel sheet, split it and insert data into teradata table. . How can I do it? Here an example of the scennario:

QUERYPATH:

/content/folder[@name='AAAAA AAAA']/folder[@name='A.B.C.']/folder[@name='AreaA']/folder[@name='Sub Area ABC']/folder[@name='GroupBB']/analysis[@name='Final elementJK']

/content/folder[@name='AAAAA AAAA']/folder[@name='A.B.C.']/folder[@name='AreaB']/folder[@name='Sub Area A.B.C.']/report[@name='Final elementHJ']

/content/folder[@name='AAAAA AAAA']/folder[@name='A.B.C.']/folder[@name='AreaC']/folder[@name='Sub BCD']/analysis[@name='Final elementFG']

id A| AAAAAAAAAA  |idArea|Area  |idSubArea| SubArea  |idGroup | Group         |  Final Element   |
 112| AAAAAAAAAA  |  22  |AreaC |   221   | Sub BCD  |  2216  | GroupA        | Final elementFG  |
 112| BDHDSKDDDD  |  39  |AreaA |   393   | Sub  ABC |  3931  | GroupBB       | Final elementJK  |
 112| AAAAAAAAAA  |  22  |AreaC |   222   | Sub BCD  |  2217  |Final ElementLL| Final elementLL  |
 112| EEEEEEEEEE  |  11  |AreaB |   114   |Sub A.B.C.|  1142  |Final elementHJ| Final elementHJ  |

There's always an Area and SubArea Value, Group and FinalElement usually are "new values" and also usually there is no a group value, so in this case I copy the FinalElement valu (example: Final elementLL, Final elementHJ )

Solution

  • There is no split function in PowerCenter. You'll need to use a combination of INSTR and SUBSTR functions to extract the appropriate values.

    Please also share (apart from the problem definition) your attempts on solving it and results you have. It will make it easier to help you achieve the results.