Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Subfield with space as delimeter

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.

Labels (1)
2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

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.

View solution in original post

Highlighted
Contributor III
Contributor III

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];

 

View solution in original post

5 Replies
Highlighted
Contributor III
Contributor III

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

Can you share sample data?
Highlighted
Partner
Partner

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.

View solution in original post

Highlighted
Creator II
Creator II

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.

 

Highlighted
Contributor III
Contributor III

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];

 

View solution in original post

Highlighted
Creator II
Creator II

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.