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

ETL Script - how split data separate with ';'

Wed, 06 Jul 2016 12:52:32 GMT

Hi All,

i've this table:

Functional area	Application Name	Keywords
FUN1	Key1	Key1; Key2
FUN1	App	App; Actual; Flash
FUN2	Daily View	Key1; Analysis
FUN2	TLC Key1 Analysis	Key1; Telecom; MMS
FUN3	Order in Take	Ordered
FUN3	FUN2les 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 area	Application Name	Keywords
FUN1	Key1	Key1
FUN1	Key1	Key2
FUN1	App	App
FUN1	App	Actual
FUN1	App	Flash
FUN2	Daily View	Key1
FUN2	Daily View	Analysis
FUN2	TLC Key1 Analysis	Key1
FUN2	TLC Key1 Analysis	Telecom
FUN2	TLC Key1 Analysis	MMS
FUN3	Order in Take	Ordered
FUN3	FUN2les Analysis	Backlog
FUN3	FUN2les Analysis	Forward Orders

I've to do this in ETL.

How can i do it with script?

thanks!!

Silvia

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

antoniotiman — Wed, 06 Jul 2016 14:09:34 GMT

Hi Silvia,

in script use

SubField(Keywords,';') as Keywords

...

Regards,

Antonio

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

sunny_talwar — Wed, 06 Jul 2016 14:11:14 GMT

Try this:

LOAD [Functional area],

[Application Name],

SubField(Keywords, '; ') as Keywords

FROM

[db_split.xlsx]

(ooxml, embedded labels, table is Sheet1);

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

antoniotiman — Wed, 06 Jul 2016 14:12:43 GMT

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.

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

Wed, 06 Jul 2016 14:56:15 GMT

Thank you very much!

Silvia

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

Wed, 06 Jul 2016 14:56:34 GMT

thank you very much.

Silvia