topic Re: Subfield with space as delimeter in QlikView

Subfield with space as delimeter

Black_Hole — Sat, 16 Nov 2024 03:15:56 GMT

Hello all,

In my table, I have a field having values with this kind of syntax:

Var0= T025798 2019 194918225 MOTO AME

I would like to recuperate in a variable each value.

For example: Var1=T025798 & Var2=2019 & Var3=194918225 etc.

So I tried the function subfield in my script:

SubField(Var0,' ',1) AS Var1,

SubField(Var0,' ',2) AS Var2,

SubField(Var0,' ',3) AS Var3,

etc.

But I obtain a result only for the first, Var1. I think that this is due because the length between the values is not equal.

Please could you help me to resolve my problem.

Thank you in advance for your help.

Re: Subfield with space as delimeter

awaisqureshi — Tue, 18 Jun 2019 15:35:26 GMT

Seems like either subfield or left/right should get you the result you're looking for.

Can you share sample data?

Re: Subfield with space as delimeter

kuba_michalik — Tue, 18 Jun 2019 15:37:24 GMT

It's not about the unequal length, it's because there is more than one space between the values. Basically Subfield(Var0,' ',2) will return what's between the first and second space (the character). And obviously there is nothing there because one is right after the other 😉

You could preprocess the values so the consecutive spaces are collapsed to a single one. There is no inbuilt function to do this (that I know) but you could create a map:

map:
MAPPING LOAD
Repeat(' ', RecNo()),
' '
Autogenerate 10; // 10 is the maximum number of consecutive spaces you can have, raise as needed

and then apply it using MapSubstring function:
MapSubstring('map', Var0)

You can either wrap this with individual Subfield() calls or do a initial load just for collapsing the spaces and do the Subfield in a preceding load or a resident load.

Re: Subfield with space as delimeter

Black_Hole — Tue, 18 Jun 2019 15:55:35 GMT

Hi @kuba_michalik , @awaisqureshi ,

Thank you for your reply.

@awaisqureshi : I put a sample in the attached file.

@kuba_michalik : I will try to see if I can resolve my problem in this way.

Thank you in advance for your help.

Re: Subfield with space as delimeter

awaisqureshi — Tue, 18 Jun 2019 17:03:38 GMT

Hi @Black_Hole ,

Here is @kuba_michalik's suggestion applied to your needs.

map: Mapping LOAD Repeat(' ', RecNo()), ' ' Autogenerate 10; // 10 is the maximum number of consecutive spaces you can have, raise as needed Table1: LOAD MapSubString('map', VOUCHER_TEXT) as Var0, subfield(MapSubString('map', VOUCHER_TEXT),' ',1) as Var1, subfield(MapSubString('map', VOUCHER_TEXT),' ',2) as Var2, subfield(MapSubString('map', VOUCHER_TEXT),' ',3) as Var3 FROM [directory];

Re: Subfield with space as delimeter

Black_Hole — Wed, 19 Jun 2019 10:40:22 GMT

Hello @kuba_michalik , @awaisqureshi ,

I tried the solution using the function Autogenerate 10 with my file and it's a great success !

Thanks @awaisqureshi for taking the time to apply the @kuba_michalik's solution to my case.

Thank you very much for your help.