Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I think this should be an easy one but I still cannot find a way to do it.
I have two tables linked by ID field. Table1 is about the basic information of the IDs, and Table2 is a transaction table. There is a Region field in Table1. There are various other fields in both tables.
I wish to list out the Regions where none of the IDs from these Regions has ever shown up in Table2.
How do I do it?
Thanks in advance for any help!
Thanks Lech for trying to help. I was having difficulties understanding the "Mapping" but I finally found a way to do what I wanted:
if(aggr(count(ID), Region)=0,Region)
This dimension expression seems to be able to successfully list all the Regions that haven't got a transaction yet...
Hi,
first simple assumption is that ID represent region or any other dimension beeing a lower granularity of Region hierarchy data.
The simplest way of doing it is to flag records in dimensional table.
like:
FlagMap:
Mapping
Load
ID,
1
Resident
Table2(Fact)
;
then in Table1 you applymap like this
Load
*,
ApplyMap('FlagMap',ID,0) as Flag
resident
Table1 (Dimensions)
then simply refer to field "Flag" and:
0 - represents excluded
1 - represents included or shown up in Fact
Solution based on expressions can also be achieved but this would be the easiest and the most efficient one i quess.
regards
Lech
Hi Again,
see example attached.
It is nasty code but works ok.
hth
Lech
Thanks Lech for trying to help. I was having difficulties understanding the "Mapping" but I finally found a way to do what I wanted:
if(aggr(count(ID), Region)=0,Region)
This dimension expression seems to be able to successfully list all the Regions that haven't got a transaction yet...