How to get distinct count of a column common in two tables

[#1]

Hi,

     I have 2 tables P_details and A_details.

   P_details has 3 columns - P_No, P_Amount and P_Region. Here P_No is unique.

    A_details has 4 columns - P_No, A_Age, A_Gender and Req_No. Here Req_No is unique. P_details has 30 records and A_details has 32 records. There are 17 P_No common in both the tables. There are some P_No that are present in each of these table and are no common.

There is 1 tab dedicated to each of these. 1 tab displays the count(distinct(P_No)) and sum(P_Amount) for P_Region. It also displays the

count(distinct(Req_No)) for each of the P_Region. At the top it also displays total P_No. and total P_Amount in Text field.

2nd tab displays count(distinct(Req_No)) and its other columns.

My Problem is the one in bold. I get 41 instead of 30.

In short, I get in my count duplicate records.  Left join or Right join is not my solution. I want all the records from both the tables, but I should have control to display what I want. Please help how this can be achieved.

I have attached the sample data and the qv file.

Thanks

Lax

[anderslinden]

Use a set expression, for example like this

=

count({$<P_Region={East,North,South}>} DISTINCT(P_No))

[#3]

Hi,

     If I concatenate this tables while extraction then I don't see the data for common P_No. Why is it so ? My requirement is precisely that.

Thanks

Lax

[robertomontanar]

I think you get this problem because you are using a "key field" (P_No) that is in two tables.

There is often some ambiguity with key fields, and the suggestion is never use them in "count" expressions

The simpler way to solve the problem, is to load a new field, with a unique name

(ex.: LOAD `P_No` as P_No_inPdetail ...) and counting this field, instead of "P_No"

Hope this help you

Be happy