Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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!

Tags (2)
9 Replies
MVP
MVP

Re: Subfield and Distinct Issue

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

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.

MVP
MVP

Re: Subfield and Distinct Issue

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

Re: Subfield and Distinct Issue

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

MVP
MVP

Re: Subfield and Distinct Issue

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

Not applicable

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

MVP
MVP

Re: Subfield and Distinct Issue

But still why we can't use Distinct here?

MVP
MVP

Re: Subfield and Distinct Issue

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

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.

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

Community Browser