Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shimon_klick
New Contributor

Counting and filtering, SubField()

Hi,

I want to know if there's a way i can use Subfield() funtion to keep the second word in a 2 word field, if it contains 1 it should remain as it is.

Eg,

Apple red = red

Sky blue = blue

Yellow = yellow

Pink = pink.

I used the code,

[CategoryName] = ' ',(SubField(CategoryName , ' ' , 2) As new_column.

But the result I am getting is only red and blue. It is not counting the single word fields.

I also used, if condition,

=If(CategoryName = ' ' ,(SubField(CategoryName,' ',2), CategoryName)

But it doesnt seem to work correctly.

1 Solution

Accepted Solutions
jayshrinipurte
Contributor

Re: Counting and filtering, SubField()

Hi Shimon,

Hope so it will help u...

If(IsNull(SubField(Category,' ',2)), SubField(Category,' ',1),SubField(Category,' ',2))

Regards,

Jayshri

7 Replies
MVP
MVP

Re: Counting and filtering, SubField()

>>[CategoryName] = ' ',(SubField(CategoryName , ' ' , 2) As new_column.

Does not make senses syntactically. Are you missing an If(?


>>=If(CategoryName = ' ' ,(SubField(CategoryName,' ',2), CategoryName)

Missing a closing bracket. Where are you trying to use this?

andrei_delta
Contributor II

Re: Counting and filtering, SubField()

hi,

it;s normal that you don;t have any value shown for "If(CategoryName = ' ' ,(SubField(CategoryName,' ',2), CategoryName)"

this is what you have:

Apple red = red

Sky blue = blue

Yellow = yellow

Pink = pink.

there is no word after the blank space for yellow and pink. you want a value to be returned you have to use subfield(CategoryName,' ') and will get you Yellow and Pink

MVP
MVP

Re: Counting and filtering, SubField()

To get the last subfield, use -1 as the subfield number -

=SubField(CategoryName, ' ', -1)

This will return the 2nd subfield for the two word values and will also work for 1 word value.

Re: Counting and filtering, SubField()

May be try this

SubField(' ' & Category, ' ', -1) as Color

Sample Script

Table:

LOAD *,

SubField(' ' & Category, ' ', -1) as Color;

LOAD * INLINE [

    Category

    Apple red

    Sky blue

    Yellow

    Pink

];


Capture.PNG

jayshrinipurte
Contributor

Re: Counting and filtering, SubField()

Hi Shimon,

Hope so it will help u...

If(IsNull(SubField(Category,' ',2)), SubField(Category,' ',1),SubField(Category,' ',2))

Regards,

Jayshri

shimon_klick
New Contributor

Re: Counting and filtering, SubField()

Thanks Jayshri, this works!

Re: Counting and filtering, SubField()

This did not work SubField(' ' & Category, ' ', -1) as Color

Community Browser