Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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];
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.
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.
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];
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.