Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qvraj123
Contributor II

charindex in Qlik with LTRIM

Hi All,

I am trying to extract the string - I got this working in SQL Server but for some reason I have to move this logic to Qlik to use it in a filter

CharIndex, LTRIM, ISNULL, NULLIF - trying to use subfield in place of charindex

UPPER(LEFT(LTRIM(A.[Short_description]), ISNULL(NULLIF(CHARINDEX(' ', LTRIM(A.[Short_description])) - 1, -1), LEN(LTRIM(A.[Short_description]) ))) )

I look for the first space from the left side and take the string from the beginning or from the left side until the first space in the string

attached some records - can anyone please assist - thanks a lot for your time

thanks alot

1 Solution

Accepted Solutions

Re: charindex in Qlik with LTRIM

Try this

Table:

LOAD Ticket_Number,

    Title,

    PurgeChar(SubField(Title, ' ', 1), Chr(34)) as TESTING

FROM

[..\..\Downloads\Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

4 Replies

Re: charindex in Qlik with LTRIM

Try this

Table:

LOAD Ticket_Number,

    Title,

    PurgeChar(SubField(Title, ' ', 1), Chr(34)) as TESTING

FROM

[..\..\Downloads\Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

qvraj123
Contributor II

Re: charindex in Qlik with LTRIM

Thanks a lot Sunny, - I have some records where the string starts after 1 space for example

" CRITICAL : abdyafyffddfffdasf" - how to remove the space that is at the beginning or immediately after the " from the left

I do not want to use TRIM as it will take off the spaces - right

thanks a lot again

Highlighted
atkinsow
Valued Contributor II

Re: charindex in Qlik with LTRIM

Have you tried Ltrim?

qvraj123
Contributor II

Re: charindex in Qlik with LTRIM

Thank you, I was able to use the INDEX to find the first occurrence of the ' '  with an IF condition

IF(INDEX(Title, ' ',1)=2, UPPER(SubField(Title, ' ', 2)), UPPER(PurgeChar(SubField(Title, ' ', 1), Chr(34)) ) )  AS test

Thanks a lot