Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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;
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?
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?
This code is working for all length of string in place of the Available and any other string as well on ths same position.
I would expect as always from 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