Subfield and Distinct Issue

[#1]

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!

[MK_QSL]

CrossTable(Data,Goal_Final,1)

Load

  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

];

[#3]

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.

[MK_QSL]

Inline is just for example...

you can use in real scenario as below

CrossTable(Data,Goal_Final,1)

Load

     ID,

     Goal1, Goal2, Goal3,

     OtherField...

From...TableName;

[#5]

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

[MK_QSL]

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

[#7]

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)).

[MK_QSL]

But still why we can't use Distinct here?

[swuehl]

Subfield() or a CROSSTABLE LOAD prefix will only duplicate the attribute fields.

Maybe you can transform your data model, so that any fact will be in a table linked by a key to your table with GOALs.

[#10]

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.

Sum(Value) instead of  sum(aggr(sum(Distinct Value),ID)).