topic Re: Subfield and Distinct Issue in QlikView

Subfield and Distinct Issue

Wed, 22 Jul 2015 15:25:45 GMT

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

MK_QSL — Wed, 22 Jul 2015 15:33:28 GMT

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

];

Re: Subfield and Distinct Issue

Wed, 22 Jul 2015 15:39:51 GMT

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

MK_QSL — Wed, 22 Jul 2015 15:44:13 GMT

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;

Re: Subfield and Distinct Issue

Wed, 22 Jul 2015 15:49:42 GMT

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

Re: Subfield and Distinct Issue

MK_QSL — Wed, 22 Jul 2015 17:41:54 GMT

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

Re: Subfield and Distinct Issue

Wed, 22 Jul 2015 18:02:54 GMT

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

MK_QSL — Wed, 22 Jul 2015 18:11:47 GMT

But still why we can't use Distinct here?

Re: Subfield and Distinct Issue

swuehl — Wed, 22 Jul 2015 18:24:53 GMT

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.

Re: Subfield and Distinct Issue

Wed, 22 Jul 2015 18:29:59 GMT

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