Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (1)
1 Solution

Accepted Solutions

Re: ETL Script - how split data separate with ';'

Try this:

LOAD [Functional area],

     [Application Name],

     SubField(Keywords, '; ') as Keywords

FROM

[db_split.xlsx]

(ooxml, embedded labels, table is Sheet1);


Capture.PNG

5 Replies
antoniotiman
Honored Contributor III

Re: ETL Script - how split data separate with ';'

Hi Silvia,

in script use

SubField(Keywords,';') as Keywords

...

Regards,

Antonio

Re: ETL Script - how split data separate with ';'

Try this:

LOAD [Functional area],

     [Application Name],

     SubField(Keywords, '; ') as Keywords

FROM

[db_split.xlsx]

(ooxml, embedded labels, table is Sheet1);


Capture.PNG

antoniotiman
Honored Contributor III

Re: ETL Script - how split data separate with ';'

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

Re: ETL Script - how split data separate with ';'

Thank you very much!

Silvia

Not applicable

Re: ETL Script - how split data separate with ';'

thank you very much.

Silvia

Community Browser