topic Re: Re: Common Parts (records) among different databases, but from a same table in QlikView

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

dmohanty — Wed, 26 Mar 2014 06:55:41 GMT

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!

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

tresB — Wed, 26 Mar 2014 07:09:15 GMT

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.

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

dmohanty — Wed, 26 Mar 2014 09:11:05 GMT

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!

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

tresB — Wed, 26 Mar 2014 09:54:45 GMT

PFA. Hope that helps you.

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

dmohanty — Wed, 26 Mar 2014 10:40:19 GMT

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!

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

tresB — Wed, 26 Mar 2014 10:50:02 GMT

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)'.

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

dmohanty — Wed, 26 Mar 2014 10:57:40 GMT

Great help Tresesco. Thanks again.

I am more clear into this now.

Cheers and have a good day!