Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ETL Script - how split data separate with ';'

Hi All,

i've  this table:

   

   

Functional areaApplication NameKeywords
FUN1Key1Key1; Key2
FUN1AppApp; Actual; Flash
FUN2Daily ViewKey1; Analysis
FUN2TLC Key1 AnalysisKey1; Telecom; MMS
FUN3Order in TakeOrdered
FUN3FUN2les Analysis

Backlog; Forward Orders


The field Keywords contains words separate by ';' semicolon.

I need to split this words and duplicate the rows.

At the end, i want obtain this result:

   

Functional areaApplication NameKeywords
FUN1Key1Key1
FUN1Key1Key2
FUN1AppApp
FUN1AppActual
FUN1AppFlash
FUN2Daily ViewKey1
FUN2Daily View Analysis
FUN2TLC Key1 AnalysisKey1
FUN2TLC Key1 AnalysisTelecom
FUN2TLC Key1 AnalysisMMS
FUN3Order in TakeOrdered
FUN3FUN2les AnalysisBacklog
FUN3FUN2les AnalysisForward Orders


I've to do this in ETL.

How can i do it with script?

thanks!!

Silvia

1 Solution

Accepted Solutions
sunny_talwar

Try this:

LOAD [Functional area],

     [Application Name],

     SubField(Keywords, '; ') as Keywords

FROM

[db_split.xlsx]

(ooxml, embedded labels, table is Sheet1);


Capture.PNG

View solution in original post

5 Replies
antoniotiman
Master III
Master III

Hi Silvia,

in script use

SubField(Keywords,';') as Keywords

...

Regards,

Antonio

sunny_talwar

Try this:

LOAD [Functional area],

     [Application Name],

     SubField(Keywords, '; ') as Keywords

FROM

[db_split.xlsx]

(ooxml, embedded labels, table is Sheet1);


Capture.PNG

antoniotiman
Master III
Master III

From QV help :

Nella versione a tre parametri, questa funzione di script restituisce una determinata sottostringa da una stringa più grande s con il delimitatore delimiter. index è un numero intero opzionale che indica quale sottostringa deve essere restituita. Se index è omesso quando subfield viene utilizzato in un'espressione di campo in un'istruzione load, la funzione subfield indurrà l'istruzione load a generare automaticamente un record di dati di input completo per ogni sottostringa trovata in s.

Not applicable
Author

Thank you very much!

Silvia

Not applicable
Author

thank you very much.

Silvia