spliting the field value

[manoranjan_d]

Hi all,

I want to split the field value in to several fiedl

below is the sample and output pls let me know how to do this in the qv script

Field name1

1001/1002/1003/1004 - 1005/1006 - Available ( 100% )

output

f1	f2	f3	f4	f5	f6	f7	f8
1001	1002	1003	1004	1005	1006	Available	1.0

note: last field percentage has to be calculated example 100% is 100/100 =1 and it shd be in 1.0 value

[fvelascog72]

Hi,

Try this:

DATA:

LOAD

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 1) as DATA1,

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 2) as DATA2,

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 3) as DATA3,

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 4) as DATA4,

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 5) as DATA5,

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 6) as DATA6,

  Subfield(Replace(Replace(Replace(data, '-', '/'), ' ',''), '(','/'),'/', 7) as DATA7,

  Num(TextBetween(data, '(', '%',1)/100, '##0,0') as DATA8

INLINE [

data

1001/1002/1003/1004 - 1005/1006 - Available ( 100% )

];

Saludos.

[its_anandrjs]

For percentage do this way  num( Left(SubField(Trim(Str),' ',7),3)/100,'#.#') as f8

LOAD
SubField(Trim(Str),'/',1) as f1,
SubField(Trim(Str),'/',2) as f2,
SubField(Trim(Str),'/',3) as f3,
SubField(Replace(Trim(Str),' - ','/'),'/',4) as f4,
SubField(Replace(Trim(Str),' - ','/'),'/',5) as f5,
SubField(Replace(Trim(Str),' - ','/'),'/',6) as f6,
SubField(SubField(Replace(Trim(Str),' - ','/'),'/',7),' ',-4) as f7,
num( Left(SubField(Trim(Str),' ',7),3)/100,'#.#') as f8,*;
LOAD * Inline
[
Str
1001/1002/1003/1004 - 1005/1006 - Available ( 100% )
];

Find the attached as well

[vishsaggi]

May be try this?

Table1:

LOAD NewField, 'Field' & RowNo() AS Attributes, IF(NewField = 100, NewField/100, NewField) As TransformedField;

LOAD *, Replace(Subfield(Subfield(Subfield(FieldName1, '/'), '-'), '('), '% )', '') AS NewField INLINE [

FieldName1

1001/1002/1003/1004 - 1005/1006 - Available ( 100% )

];

Final:

Generic Load NewField, Attributes, TransformedField

Resident Table1;

Drop Table Table1;

[manoranjan_d]

Thanks anand supposeif there is other than "Available"  word is not there example we have more more characther len of other than availble how to do this in the f7 field?

[manoranjan_d]

i agree available is fine in he example suppose if we no havet available or some other words like

xxxxxxxxxxxxxx

manoranjan

anannd liek that

dynamically it have to take that word in f7 field?

[its_anandrjs]

This code is working for all length of string in place of the Available and any other string as well on ths same position.

[Anil_Babu_Samineni]

I would expect as always from marcowedel‌

[MarcoWedel]

Hi,

maybe another solution might be as well:

mapReplDelim:
Mapping
LOAD Mid(Delimiters,IterNo(),1), '/' Inline "
Delimiters
.,;-()[]{}
" (delimiter is '\t') While IterNo()<= Len(Delimiters);

table1:
LOAD [Field name1],
    Trim(SubField(txt,'/',1)) as f1,
    Trim(SubField(txt,'/',2)) as f2,
    Trim(SubField(txt,'/',3)) as f3,
    Trim(SubField(txt,'/',4)) as f4,
    Trim(SubField(txt,'/',5)) as f5,
    Trim(SubField(txt,'/',6)) as f6,
    f7,
    f8;
LOAD *,
    Trim(SubField(Mid([Field name1],Pos+1),')',1)) as f8,
    Trim(Mid([Field name1],Index(txt,'/',6)+1,Pos-Index(txt,'/',6)-1)) as f7;
LOAD *,
    MapSubString('mapReplDelim',[Field name1]) as txt,
    Index([Field name1],'(',-1) as Pos  
Inline "
    Field name1
    1001/1002/1003/1004 - 1005/1006 - Available ( 100% )
    1007/1008/1009/10010-10011/10012-Not Available ( 50% )
    10013/10014/10015/10016(10017)10018-xxxxxxxxxxxxxx ( 30% )
    10019/10020,10021,10022,10023/10024-Some other status ( 20% )
    10025{10026}10027/10028-10029/10030- Status including some (brackets) ( 10% )
    10031 - 10032 - 10033 - 10034 - 10035 - 10036 - Very-long-status including spaces. (brackets) and ; other,[special] {characters}  ( 40% )
    10037.10038,10039-10040(10041)10042- ( 60% )
" (delimiter is '\t');

hope this helps

regards

Marco