Common Parts (records) among different databases, but from a same table

[dmohanty]

Hi All,

I have 14 databases, let say A,B,C....N. I have a table called PartTable which is there in all the databases (same table structure) but with different data. It has a field called Part_No.

I have stored this table into a QVD into 14 different folders i.e. A,B,C,....N

Now in QV, I want to show the common parts from Part_No from all 14 databases (folders).

Also, I need to comapre the Part Nos for 'A' with rest of the Databases i.e from B to N.

How to do a scripting for this? Please help.

Regards!

[tresesco]

While loading all these tables use a flag  before the get concatenated like:

Final:

Load

          *,

          'A' as Flag

From A.qvd (qvd);

Load

          *,

          'B' as Flag

From B.qvd (qvd);

....

Now all data in a single table, you can do the analysis at the front end using set analysis.

[dmohanty]

Hi Tresesco,

Thanks for the help.

I have loaded the data as suggested. Also, Flag is now available in QV.

Could you please show me some examples, how to use the Flag in Set Analysis to do the comparison?

Regards!

[tresesco]

PFA. Hope that helps you.

[dmohanty]

Thank you once again Tresesco.

But still I am not sure what the expression is doing. Basic Set Analysis I know, but I am not much into the p() and e() of Set Analysis.

Could you please clarify, what information we are getting from the expression results?

Regards!

[tresesco · Accepted solution]

Sum({<ID=p({<Flag={'A'}>}ID)*p({<Flag={'B'}>}ID)*p({<Flag={'A'}>}ID)>}Amt)

This set sums Amt for those IDs where Flag=A and Flag=B and Flag=C. '*' is used in set as INTERSECTION , i.e -'and' . In other words, IDs would be taken into consideration that are there in all (A,B,C) types of flags.

In the sample data:

Load * Inline [

ID, Amt, Flag

1, 100, A

2, 200, A

3, 300, A

2, 400, B

1, 500, C

2, 600, C]

The only ID that is there in all flag types is 2. Now the expression sums the Amt for ID -2, and they are:

2, 200, A

2, 400, B

2, 600, C

hence the sum =1200.

This is how you get your 'Common Parts (records)'.

[dmohanty]

Great help Tresesco. Thanks again.

I am more clear into this now.

Cheers and have a good day!