Compare 2 tables and show how similar they are

[acholchemit]

Hi,

I have a bill of materials tables that will contain the following key pieces of information.

1. BOM Code

2. Stock Code

Each BOM will have one or more stock codes. Example data as below

BOM Code	Stock Code
A1	WH1
A1	BH4
A1	OP3
A2	OP3
A2	BH4
A2	ZA6

I am trying to create a way to select a single BOM Code and have Qlik compare all of the other BOM codes to show how similar they are.  The first way of doing this is to show how many common ingredients there are.

So far I have filed in trying to use alternate states with the P function and I have also failed in trying to use two tables in the load joined by the common stock codes.  

I am trying to get to a simple formula like the pseudocode below.

Count([Compared Common BOM Stock Code]) / Count([Selected BOM Stock Code])  = Similarity Measure

So for the table above I'd have A1 = 3 and A2 = 2 i.e. 2/3 = 0.66

It seems like it should be fairly straightforward but so far a viable solution has alluded me.

Any help appreciated.

[PriyankaShivhare]

U need to do the Intersection between Compared and Selected BOM Code:

May be Something Like This

count({<[Stock Code]=p({<[BOM Code]={'$(=only([BOM Code]))'}>}[Stock Code])>*<[Stock Code]=p({<[BOM Code]={'$(=only({G1}[BOM Code]))'}>}[Stock Code])>}Distinct [Stock Code])/
count({<[BOM Code]={'$(=only([BOM Code]))'}>}Distinct [Stock Code])

where G1 is the Alternate State.

let me know if this help

Thanks,

Priyanka

[acholchemit]

Hi Priyanka,

Thanks for this.  

Can you explain how this would be setup on screen so that the alternate state is used?  

I'm trying to visualise it so that I can configure the objects properly and I'm not sure if I have followed your line of thinking.

Thanks

[PriyankaShivhare]

Basically its a intersection of selected and compared set of records

If u have selected A1 -BOM code : default state

And u have selected A2 - BOM code : Alternate state(G1)

Then above mentioned formula would work...

<Stock code of selected BOM code(default state) >*<Stock code of compared BOM code(alternate state)>/Stock code of selected BOM code(default state

Hope this helps.

Thanks 

Priyanka