Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
acholchemit
Contributor II
Contributor II

Compare 2 tables and show how similar they are

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 CodeStock Code
A1WH1
A1BH4
A1OP3
A2OP3
A2BH4
A2ZA6

 

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.

Labels (5)
3 Replies
PriyankaShivhare
Creator II
Creator II

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
Contributor II
Contributor II
Author

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
Creator II
Creator II

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