9 Replies Latest reply: Jul 22, 2015 2:29 PM by Marcelo Hernandes

# Subfield and Distinct Issue

Hi!

I have the following table:

On QV, I'd like to see these situations:

Actually, I've achieve this merging the columns Goa1, Goal2 and Goal3 (Goal1&','&Goal2&','&Goal3 as Goal4) and then applying an subfield function, generating the field Goal Final ((SubField(Goal4,',') as Goal_Final).

But I need the same resulting in a way that the expression Count(ID) is equal 5 (the same number of IDs), WITHOUT DISTINCT in the same expression.

How is it possible?

Tks!

• ###### Re: Subfield and Distinct Issue

CrossTable(Data,Goal_Final,1)

ID,

If(Len(Trim(Goal1))=0,Null(), Goal1) as Goal1,

If(Len(Trim(Goal2))=0,Null(), Goal2) as Goal2,

If(Len(Trim(Goal3))=0,Null(), Goal3) as Goal3

Inline

[

ID, Goal1, Goal2, Goal3

1, A1, A2, A3

2, A1, A3,

3, A2, ,

4, A3, A2,

5, A1, A2

];

• ###### Re: Subfield and Distinct Issue

Hello Manish,

I've gave an simple example. In the real situation, I have thousands of ID fields. I can't assemble them in an Inline table.

• ###### Re: Subfield and Distinct Issue

Inline is just for example...

you can use in real scenario as below

CrossTable(Data,Goal_Final,1)

ID,

Goal1, Goal2, Goal3,

OtherField...

From...TableName;

• ###### Re: Subfield and Distinct Issue

Ok, understood. But the count(ID) (without distinct) now is 10, not 5.

• ###### Re: Subfield and Distinct Issue

What is the logic behind not using Distinct? Please explain !

• ###### Re: Subfield and Distinct Issue

Sometimes, I have to sum one column values. In these cases, when we have this situation of subfield, I have to use an expression with aggr. It's the situation I'm trying to avoid here, just to keep an simple expression (sum(Value)).

• ###### Re: Subfield and Distinct Issue

But still why we can't use Distinct here?

• ###### Re: Subfield and Distinct Issue

We can. Actually, I always use Distinct. I just want to know if there is another way to deal with this kind of subfield's consequence.