topic Re: Help proving a one to one relationship between 2 values/columns in Connectivity & Data Prep

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

Stephens — Mon, 17 May 2021 15:31:22 GMT

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!

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

jwjackso — Mon, 17 May 2021 16:26:56 GMT

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];

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

janexueer — Mon, 17 May 2021 17:19:49 GMT

I have the same issue.

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

janexueer — Mon, 17 May 2021 17:20:33 GMT

Thank you.

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

Stephens — Mon, 17 May 2021 18:08:56 GMT

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

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

jwjackso — Mon, 17 May 2021 18:35:08 GMT

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

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

Stephens — Mon, 17 May 2021 19:50:44 GMT

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!