Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Subfield and Distinct Issue

Hi!

I have the following table:

Screen Shot 07-22-15 at 12.17 PM.JPG

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

Screen Shot 07-22-15 at 12.17 PM 001.JPG

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!

9 Replies
MK_QSL
MVP
MVP

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

];

Not applicable
Author

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
MVP
MVP

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;

Not applicable
Author

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

MK_QSL
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

But still why we can't use Distinct here?

swuehl
MVP
MVP

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.

Not applicable
Author

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