topic Re: Split delimiter in QlikView

Split delimiter

ttal7878 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I have a column (Site) that contains fields like "Qatar_7-newsuk-thesun",

I was asked to display 2 new column (column A + column B) by dividing column "Site" by delimiter "-"

The division will be until the first dash

The final result should be:

the data displayed in column A : Qatar_7

the data displayed in column B : newsuk-thesun

I have attached an example qvw

Re: Split delimiter

Miguel_Angel_Baeyens — Wed, 14 Feb 2018 15:26:11 GMT

Using

...

SubField(Site, '-', 1) AS ColumnA,

SubField(Site, '-', 2) AS ColumnB,

...

Will work if the data is consistent, i.e.: no misplaced "-" that you don't want to filter in that exact position

Re: Split delimiter

ttal7878 — Wed, 14 Feb 2018 15:43:42 GMT

Thank Miguel for your replay,

But my intention is, the division will be up to the first dash, and then to display the information as it is,

for example:

Qatar_7-newsuk-thesun-

Column A: Qatar_7

Column B: newsuk-thesun

Re: Split delimiter

Miguel_Angel_Baeyens — Wed, 14 Feb 2018 15:51:16 GMT

Use instead for example:

Mid(SIte, Index(SIte, '-', 1)+1) AS ColumB

Re: Split delimiter

Anil_Babu_Samineni — Wed, 14 Feb 2018 15:51:34 GMT

Can you provide expected result set from this row? Chile_Desktop34-msn-outlook-chile

Re: Split delimiter

ttal7878 — Wed, 14 Feb 2018 15:57:45 GMT

Thank you!

Re: Split delimiter

ttal7878 — Thu, 15 Feb 2018 12:57:33 GMT

mbaeyens‌

Is it possible that when the field does not contain "-", a blank value will be inserted into column B?

For example, the field "VE_30" doesn't contain "-" , therefore a blank value will enter column B

Re: Split delimiter

Miguel_Angel_Baeyens — Thu, 15 Feb 2018 14:03:07 GMT

Yes, you will need to add a conditional that if the expression above so if the Len() of the result = 0, then ' ', otherwise Mid(...)

Re: Split delimiter

Miguel_Angel_Baeyens — Thu, 15 Feb 2018 14:11:11 GMT

Or with a proper syntax:

If(Len(Mid(SIte, Index(SIte, '-', 1)+1) = 0, ' ', Mid(SIte, Index(SIte, '-', 1)+1)) AS ColumB

Re: Split delimiter

ttal7878 — Thu, 15 Feb 2018 16:32:05 GMT

It was not exactly what I meant, but I managed to find a solution.

many thanks mbaeyens‌

if(Index(SIte,'-'),Mid(SIte, Index(SIte, '-', 1)+1),'') AS ColumnB