Bug with SubField and Concat function?

[Anonymous]

Hello I suspect I found a bug and would need a workaround.

I load this simple data:

LOAD * INLINE [

    City, Name, Enrollment Status

    New York, Mark, Completed

    New York, Mark, No Show

    Chicago, Larry, Completed

    Chicago, Larry, No Show

];

Then I create a Straight Table with the following dimensions:

City

Name

And the following measures:

count(DISTINCT [Enrollment Status])

SubField(concat(DISTINCT [Enrollment Status],',',Match([Enrollment Status],'Completed','No Show')),',')

concat(DISTINCT [Enrollment Status],',',Match([Enrollment Status],'Completed','No Show'))

Now the result I get is:

How comes that I get a different result in the SubField formula for Mark and Larry?

The concatenation of fields is the same for the two, as shown in the last column, but when I try to get the first value I get the wrong one for Larry.

See attached document

Thank you for any feedback

Mario

[sunny_talwar · Accepted solution]

In order to get first value you need to add the third parameter of the SubField function

SubField(concat(DISTINCT [Enrollment Status],',',Match([Enrollment Status],'Completed','No Show')),',', 1)

But I wonder, how it is picking the value without the parameter...

[Anonymous]

Thank you, Sunny