topic Re: Reverse + Regex in QlikView

Reverse + Regex

Mon, 02 Jun 2014 07:33:33 GMT

Hello,

I have a string which has patterns of Hebrew, English and numbers.

In one database I need to reverse only the English,

in other Hebrew and and numbers etc...

for example

string1:

שמש nusחמה 123

string1Fix:

שמש sun חמה 123

some one can help me?

Re: Reverse + Regex

Mon, 02 Jun 2014 10:19:18 GMT

Hi keren,

try this:

tab:

LOAD rowno() as string_id, * INLINE [

שמש nus 123 חמה

];

tab1:

load string_id, rowno() as substr_id, SubField(F1,' ') as substr Resident tab;

NoConcatenate

eng:

load string_id,substr_id, substr Resident tab1 where KeepChar(substr,'abcdefghijlkamnopqrstuwxyz')<>'';

eng_lett:

load IterNo() as letter_id,string_id,substr_id, mid(substr,IterNo(),1) as letter Resident eng while IterNo()<=len(substr);

eng_rew:

load concat(letter,'',-letter_id) as rew_eng,string_id,substr_id Resident eng_lett Group by string_id,substr_id;

drop Tables tab,eng_lett,eng;

Left join (tab1) load string_id,substr_id,rew_eng Resident eng_rew;

drop table eng_rew;

result:

load Concat(if(not IsNull(rew_eng), rew_eng, substr),' ',substr_id) as new Resident tab1 group by string_id;

drop table tab1;

Re: Reverse + Regex

Mon, 02 Jun 2014 13:17:44 GMT

TY, its excellent,

but i have more problems, that i could'nt solve on my own.

when i have 2 word in English sequentially

i need to reverse them both together.

4 example :

LY pppiD - חסום

needs to be

Dippp YL - חסום

when i have hyphen

4 example :

ידת מפוח 004-CF אופקית 4 שורות CBA ללא מעטה

needs to be as attach

tab:

LOAD rowno() as string_id, * INLINE [

LY pppiD - חסום

ידת מפוח 004-CF אופקית 4 שורות CBA ללא מעטה

];

tab1:

load string_id, rowno() as substr_id, SubField(F1,' ') as substr Resident tab;

NoConcatenate

eng:

load string_id,substr_id, substr Resident tab1 where KeepChar(substr,'ABCDEFGHIGKLMNOPQRSTUWXYZabcdefghijlkmnopqrstuwxyz')<>'';

eng_lett:

load IterNo() as letter_id,string_id,substr_id, mid(substr,IterNo(),1) as letter Resident eng while IterNo()<=len(substr);

eng_rew:

load concat(letter,'',-letter_id) as rew_eng,string_id,substr_id Resident eng_lett Group by string_id,substr_id;

drop Tables tab,eng_lett,eng;

Left join (tab1) load string_id,substr_id,rew_eng Resident eng_rew;

drop table eng_rew;

result:

load Concat(if(not IsNull(rew_eng), rew_eng, substr),' ',substr_id) as new Resident tab1 group by string_id;

drop table tab1;

Re: Reverse + Regex

Mon, 02 Jun 2014 13:33:45 GMT

Keren,

i'am sure all rules, that you can define, can be implemented. I show you how you may divide your text word by word and next letter by letter and then concatenate it again and change letter order.

You may easy find english parts.

You may for example b4 dividing to letter concatenate consequtive substrings if both are in english. Use previous() or peek() to get value from previous row.....

I hope, that my answers was helpful.

regards