topic Re: How to split a text in Script?? in QlikView

How to split a text in Script??

Thu, 25 Oct 2012 17:25:23 GMT

Good day

I have a data that gives me as below:

Invoice	Supplier	Code
1458-1459	US05421	1
1460-1461-1462	US05421	2

Is there any formula that i can split these Invoice number by the character of " - "??

It should be looks like this:

Invoice	Supplier	Code
1458	US05421	1
1459	US05421	1
1460	US05421	2
1461	US05421	2
1462	US05421	2

Tks in advance!

Re: How to split a text in Script??

MayilVahanan — Thu, 25 Oct 2012 17:27:15 GMT

Try like this

Load *,SubField(FieldName,'-') as fieldname from table;

Re: How to split a text in Script??

CELAMBARASAN — Thu, 25 Oct 2012 17:28:19 GMT

Use SubField()

Load SubField(Invoice,'-') AS Invoice, Supplier,Code

From datasource;

Info about SubField:

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Re: How to split a text in Script??

Thu, 25 Oct 2012 18:16:07 GMT

Tks a lot.