topic How to split a field based on a fixed number of bytes in QlikView

How to split a field based on a fixed number of bytes

LRAngela — Mon, 14 Nov 2022 07:40:11 GMT

hi all，

i want to split a field to some fields，E.g：

text

1如23 split to

1 如 23

i want use mid（）to run it but i found that it failed。

mid（text，2，2） it shows that 如2 . it's wrong.it should be 如.

so，i want to split a field based on a fixed number of bytes，can you help me，thanks！

Re: How to split a field based on a fixed number of bytes

MayilVahanan — Mon, 14 Nov 2022 07:52:03 GMT

In ur sample, try like Mid('1如23',2,1) <-- it gives expected result.

However, if you want dynamic, if always first character is number, you can split the in-between letters, then try like below

=mid('1如23', 2 , FindOneOf(mid('1如23',2), 1234567890)-1)

Re: How to split a field based on a fixed number of bytes

LRAngela — Mon, 14 Nov 2022 08:56:56 GMT

Thank you for your help, but mine is more complicated.

The first character is not sure, and the number of Chinese characters in the middle is not sure, but the start byte position and end position of Chinese are certain. So I want a function that can be intercepted according to the byte, is there a way.

eg: the field is test , start byte position is 2 end position is 10

test

1如果还有 3456

a如果 7890

c如 a

I have tried many methods but can't solve it, because this position is calculated by the number of bytes, not the number of characters, and mid() is intercepted by characters, so it can't be solved.

thanks a lot

Re: How to split a field based on a fixed number of bytes

MayilVahanan — Mon, 14 Nov 2022 10:30:01 GMT

If you want to get only the Chinese characters, try with Purgechar() functionality like below

LOAD *, PurgeChar(test, '0123456789abcdefghijklmnopqrstuvwxyz') as chinesetest INLINE [
test

1如果还有 3456

a如果 7890

c如 a
];

Re: How to split a field based on a fixed number of bytes

LRAngela — Tue, 15 Nov 2022 01:36:50 GMT

It is not just Chinese characters, the part to be intercepted is actually the address, there may be numbers, etc., so it needs to be intercepted according to the byte position

Re: How to split a field based on a fixed number of bytes

MayilVahanan — Tue, 15 Nov 2022 09:40:15 GMT

Can you say the expected output for below?

test

1如果还有 3456

a如果 7890

c如 a

Re: How to split a field based on a fixed number of bytes

marcus_sommer — Tue, 15 Nov 2022 16:12:23 GMT

I think I would check and categorize each char and would be starting by:

load *, if(CharID >= n1 and CharID <= n2, 'Number/AlphaNumeric/Chinese/Others', ... as CharCategory;
load *, len(Text) as Len, ord(Char) as CharID;
load Text, mid(Text, iterno(), 1) as Char, recno() as RecNo, iterno() as IterNo
from Source while len(Text) <= iterno();

Instead of a nested if-loop for the categorizing you may use a mapping. In further steps you may read the first/last char from each category, count their amount and so on to classify their content and with this information you may concat() the Char's in the end again.

Especially if there is a high variety of different content you will need a systematically approach with rather less (nested) if-loops to cut and pick here and there the relevant stuff. Further depending on the data-quality you may not be able to resolve all data - **bleep** in = **bleep** out ...

- Marcus

Re: How to split a field based on a fixed number of bytes

LRAngela — Wed, 16 Nov 2022 02:39:37 GMT

Generally speaking, I have a text file, in which each line of data is a combination of multiple fields, in which the number of bytes is 200 to 300 for the field address, and if the length is less than 100, it will be represented by a space. I just want to intercept the adress by the number of bytes, but i dont konw the function that can help me

solve this question.

Currently my script looks like this ：Mid(test, 456,40) as "Transactor CertNo", Mid(test, 496,300) as Address。

This address is misplaced, because there are other characters in the front that occupy two bytes, so mid() cannot be used, mid() is intercepted according to the character。（"Transactor CertNo" is an empty data field）

Re: How to split a field based on a fixed number of bytes

LRAngela — Wed, 16 Nov 2022 02:41:35 GMT

I can't understand what you mean very well, can you give an example or explain it in detail?

thanks

Re: How to split a field based on a fixed number of bytes

cwolf — Wed, 16 Nov 2022 10:50:21 GMT

If you ar loading from text file you can split the line in the load statement. For example:

LOAD
[@1:455] as Field1,
[@456:495] as "Transactor CertNo",
[@496:795] as Address,
[@796:n] as Rest
FROM [.\file.txt](fix, utf8, embedded labels); // file with headerline

FROM [.\file.txt](fix, utf8, no labels); // file without headerline

@startpos:endpos => startpos and endpos are byte positions!

Without a headerline you have to consider the encoding of the file. If it is a utf8 file with signature then the first line has a prefix of 3 bytes. In this case you have to move the first line for 3 Bytes.

Re: How to split a field based on a fixed number of bytes

LRAngela — Fri, 18 Nov 2022 02:00:56 GMT

wow！so cool！it ran successfully。Thank you so much you are amazing。