topic Re: spliting the field value in QlikView

spliting the field value

manoranjan_d — Tue, 05 Dec 2017 15:28:41 GMT

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

Re: spliting the field value

its_anandrjs — Tue, 05 Dec 2017 15:32:46 GMT

May be use subfiled here.

Re: spliting the field value

Anil_Babu_Samineni — Tue, 05 Dec 2017 15:35:19 GMT

Can you share real values atleast 5-10 rows to troubleshoot rather one line. Is that row is same for all ??

Re: spliting the field value

manoranjan_d — Tue, 05 Dec 2017 15:36:57 GMT

format is same for all

Re: spliting the field value

jpenuliar — Tue, 05 Dec 2017 15:38:10 GMT

try looking at Subfield function to build your fields:

SubField - script and chart function ‒ Qlik Sense

Re: spliting the field value

manoranjan_d — Tue, 05 Dec 2017 15:38:15 GMT

i used but its not working good because i have some pblm in that example space - brackets, percentage symbols

pls help me to give me in qvw files

Re: spliting the field value

jpenuliar — Tue, 05 Dec 2017 15:43:44 GMT

SubField([Field name1], '/',1) as F1,

SubField([Field name1], '/',2) as F2,

SubField([Field name1], '/',3) as F3,

SubField([Field name1], '/',4) as F4F5,

SubField([Field name1], '/',5) as F6F7F8

Re: spliting the field value

jpenuliar — Tue, 05 Dec 2017 15:45:18 GMT

worry about the F4 - F8 in the preceeding load.

again you can use subfield and or combination with purge function

Re: spliting the field value

its_anandrjs — Tue, 05 Dec 2017 15:46:29 GMT

Did you try this way

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,

SubField(Trim(Str),' ',7) as f8,

Replace(Trim(Str),' - ','/'),*;

LOAD * Inline

[

Str

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

];

For percentage, you can use 100% as it is.

Re: spliting the field value

jpenuliar — Tue, 05 Dec 2017 15:46:57 GMT

you can also force a Replace function to convert "-" to "/" :

Replace ‒ QlikView

Re: spliting the field value

fvelascog72 — Tue, 05 Dec 2017 15:52:22 GMT

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.

Re: spliting the field value

its_anandrjs — Tue, 05 Dec 2017 15:53:02 GMT

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

Re: spliting the field value

vishsaggi — Tue, 05 Dec 2017 15:53:42 GMT

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;

Re: spliting the field value

manoranjan_d — Tue, 05 Dec 2017 16:23:09 GMT

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?

Re: spliting the field value

manoranjan_d — Tue, 05 Dec 2017 16:29:12 GMT

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?

Re: spliting the field value

its_anandrjs — Tue, 05 Dec 2017 16:33:58 GMT

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

Re: spliting the field value

Anil_Babu_Samineni — Tue, 05 Dec 2017 17:46:55 GMT

I would expect as always from marcowedel‌

Re: spliting the field value

MarcoWedel — Tue, 05 Dec 2017 23:31:39 GMT

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