Help proving a one to one relationship between 2 values/columns

[Stephens]

Hello,

I need help proving that my Supplier values and my Manufacturing values have a one to one relationship, and I want to make sure no values have a one to many relationship. In other words, I want to make sure that my Supplier values always correspond to the same Manufacturing values, and that it's identifiable when a given Supplier value is connected to multiple/different Manufacturing values.

For example:

Supplier Name	Supplier Part Number	Supplier Concat Key	Manufacturer	Manufacturer Part Number	Mfr Concat Key	Facility
Sup1	AAA1	Sup1AAA1	MetalMfr	123	MetalMfr123	Plant1
Sup1	AAA1	Sup1AAA1	MetalMfr	123	MetalMfr123	Plant2
Sup1	AAA1	Sup1AAA1	MetalMfr	123	MetalMfr123	Plant3
Sup2	AAA2	Sup2AAA2	WoodMfr	AAA	WoodMfrAAA	Plant1
Sup2	AAA2	Sup2AAA2	WoodMfr	AAA	WoodMfrAAA	Plant2
Sup2	AAA2	Sup2AAA2	WoodMfr	BBB	WoodMfrBBB	Plant3
Sup3	AAA3	Sup3AAA3	PlasticMfr	11AA	PlasticMfr11AA	Plant1
Sup3	AAA3	Sup3AAA3	PlasticMfr	11AA	PlasticMfr11AA	Plant2
Sup3	AAA3	Sup3AAA3	DifferentPlasticMfr	11AA	DifferentPlasticMfr11AA	Plant3

For the example above: There are 2 instances where the Supplier Concat Key has a one to many relationship with the Mfr Concat Key (both highlighted in RED).  I am really struggling to codify this logic and to isolate/identify any examples where the Supplier Concat Key connects to multiple Mfr Concat Keys. Any help would be greatly appreciated. Thank you!

[jwjackso · Accepted solution]

The OneToManyFlag indicates which supplier has more than 1 manufacturer.

Data:
Load * Inline [
Supplier Name,Supplier Part Number,Supplier Concat Key,Manufacturer,Manufacturer Part Number,Mfr Concat Key,Facility
Sup1,AAA1,Sup1AAA1,MetalMfr,123,MetalMfr123,Plant1
Sup1,AAA1,Sup1AAA1,MetalMfr,123,MetalMfr123,Plant2
Sup1,AAA1,Sup1AAA1,MetalMfr,123,MetalMfr123,Plant3
Sup2,AAA2,Sup2AAA2,WoodMfr,AAA,WoodMfrAAA,Plant1
Sup2,AAA2,Sup2AAA2,WoodMfr,AAA,WoodMfrAAA,Plant2
Sup2,AAA2,Sup2AAA2,WoodMfr,BBB,WoodMfrBBB,Plant3
Sup3,AAA3,Sup3AAA3,PlasticMfr,11AA,PlasticMfr11AA,Plant1
Sup3,AAA3,Sup3AAA3,PlasticMfr,11AA,PlasticMfr11AA,Plant2
Sup3,AAA3,Sup3AAA3,DifferentPlasticMfr,11AA,DifferentPlasticMfr11AA,Plant3
];

Data2:
Load [Supplier Concat Key]
,IF([Mfr Concat Key Count] > 1,'1',0) as OneToManyFlag;
Load [Supplier Concat Key]
,Count(Distinct [Mfr Concat Key]) as [Mfr Concat Key Count]
Resident Data
group by [Supplier Concat Key];

[janexueer]

I have the same issue. 

[janexueer]

Thank you. 

[Stephens]

Im getting too many Invalid Expression errors when trying to implement your solution.  I think the IF formula you listed should read as:

 IF([Mfr Concat Key Count] > 1,'1','0') as OneToManyFlag

But even with this edit I am still getting Invalid Expression errors

[jwjackso]

I'm running the script successfully in Qlik Sense, see images below:

[Stephens]

You are correct, my dimensions/column headers were typed out slightly different than yours and the load was getting tripped. After making all columns exactly like the example I initially submitted, the data loaded successfully.

I need to do a little bit more testing, but this appears to yield the exact results I was looking for!