topic Re: How to Separate Text and Number Into Different Column in App Development

How to Separate Text and Number Into Different Column

BhaminiParmar12 — Tue, 18 Jun 2024 10:39:13 GMT

This is my Data I want to Split them Text into Different columns and Numbers in Different Columns and this is just examples but Text is endless and numbers are endless means no limit

Column1

BhaminiParmar12345865567

VanshikaSalat87655

KetakiRaut12

ShubhamYadav89988

JatinParmar5

Re: How to Separate Text and Number Into Different Column

maxgro — Tue, 18 Jun 2024 11:57:55 GMT

if you always have a text and then a number, the end of the text should be

Rangemin(
Replace(Index(Column1, '0', 1)-1, -1, null()),
Replace(Index(Column1, '1', 1)-1, -1, null()),
Replace(Index(Column1, '2', 1)-1, -1, null()),
Replace(Index(Column1, '3', 1)-1, -1, null()),
Replace(Index(Column1, '4', 1)-1, -1, null()),
Replace(Index(Column1, '5', 1)-1, -1, null()),
Replace(Index(Column1, '6', 1)-1, -1, null()),
Replace(Index(Column1, '7', 1)-1, -1, null()),
Replace(Index(Column1, '8', 1)-1, -1, null()),
Replace(Index(Column1, '9', 1)-1, -1, null())
)

the text

Left(Column1,
Rangemin(
Replace(Index(Column1, '0', 1)-1, -1, null()),
Replace(Index(Column1, '1', 1)-1, -1, null()),
Replace(Index(Column1, '2', 1)-1, -1, null()),
Replace(Index(Column1, '3', 1)-1, -1, null()),
Replace(Index(Column1, '4', 1)-1, -1, null()),
Replace(Index(Column1, '5', 1)-1, -1, null()),
Replace(Index(Column1, '6', 1)-1, -1, null()),
Replace(Index(Column1, '7', 1)-1, -1, null()),
Replace(Index(Column1, '8', 1)-1, -1, null()),
Replace(Index(Column1, '9', 1)-1, -1, null())
)
)

the number

Right(Column1,
Len(Column1)
-
Rangemin(
Replace(Index(Column1, '0', 1)-1, -1, null()),
Replace(Index(Column1, '1', 1)-1, -1, null()),
Replace(Index(Column1, '2', 1)-1, -1, null()),
Replace(Index(Column1, '3', 1)-1, -1, null()),
Replace(Index(Column1, '4', 1)-1, -1, null()),
Replace(Index(Column1, '5', 1)-1, -1, null()),
Replace(Index(Column1, '6', 1)-1, -1, null()),
Replace(Index(Column1, '7', 1)-1, -1, null()),
Replace(Index(Column1, '8', 1)-1, -1, null()),
Replace(Index(Column1, '9', 1)-1, -1, null())
)
)

For Qlik Sense Cloud maybe with the RegEx connector

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/RegEx-Connector.htm

Re: How to Separate Text and Number Into Different Column

MarcoWedel — Tue, 18 Jun 2024 20:14:52 GMT

Hi,

another solution might be

table1: Load *, Left(Column1,FindOneOf(Column1&'0','0123456789')-1) as Column1Text, Mid(Column1,FindOneOf(Column1,'0123456789')) as Column1Num Inline [ Column1 BhaminiParmar123458655 VanshikaSalat87655 KetakiRaut12 ShubhamYadav89988 JatinParmar5 NoNumberHere 135798642 ];

hope this helps

Marco

Re: How to Separate Text and Number Into Different Column

Gui_Approbato — Tue, 18 Jun 2024 23:51:22 GMT

Good solutions. Just adding another approach using PurgeChar and KeepChar:
Load *,

Purgechar(Column1,'0123456789') as OnlyText,

Keepchar(Column1,'0123456789') as OnlyNumbers

....