Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
];
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.
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;
Ok, understood. But the count(ID) (without distinct) now is 10, not 5.
What is the logic behind not using Distinct? Please explain !
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)).
But still why we can't use Distinct here?
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.
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)).