topic Re: Retrieve Common Records in QlikView

Retrieve Common Records

nihhalmca — Fri, 18 Sep 2015 11:15:02 GMT

Hi All,

My Data Model has 2 fact tables. Common column should be 'ID'.

Fact 1 Fact 2

ID ID

1 1

2 2

Note: I want to display 'ID' in chart expression where ID has in both the fact tables. Moreover i do not want to disturb data model for example Concatenate, Link table etc.

Thanks,

Nihhal.

Re: Retrieve Common Records

Anonymous — Fri, 18 Sep 2015 11:21:40 GMT

Well,

In this case you can create a different field name with same values, say

ID as TestID then

you can compare like:

IF(ID=TestID............sth like this..

Re: Retrieve Common Records

sunny_talwar — Fri, 18 Sep 2015 11:23:50 GMT

You want to see this where? List Box? Table Box? Straight Table?

Re: Retrieve Common Records

nihhalmca — Fri, 18 Sep 2015 11:25:31 GMT

Pivot table.

Re: Retrieve Common Records

sunny_talwar — Fri, 18 Sep 2015 11:30:44 GMT

Try with a calculated dimension: (and Select 'Suppress When Value Is Null' option on dimension tab)

=If(Len(Trim(FieldFact1)) > 0 and Len(Trim(FieldFact2)) > 0, ID)

Where FieldFact1 is a field exclusively from Fact 1 table and FieldFact2 is exclusively from Fact 2 table.

Re: Retrieve Common Records

maxgro — Fri, 18 Sep 2015 11:36:30 GMT

if Exp5 is a field in Fact 2, you can try with a calculated dimension setting null the dimension when you don't have Exp5 (else the dimension is ID)

=if(aggr(count(Exp5),ID)=0,null(), ID)

Re: Retrieve Common Records

nihhalmca — Fri, 18 Sep 2015 12:19:02 GMT

Thanks for the response Sunny.

Just i used only ID there is no measure.

Ex: Count({<Theater = {'NA'}, (Where matched records in ID from the both tables) >} ID)

Nihhal.

Re: Retrieve Common Records

sunny_talwar — Fri, 18 Sep 2015 12:21:39 GMT

Not a problem at all.

I am glad I was able to help.

Best,

Sunny