Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephens
Contributor II
Contributor II

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

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 NameSupplier Part NumberSupplier Concat KeyManufacturerManufacturer Part NumberMfr Concat KeyFacility
Sup1AAA1Sup1AAA1MetalMfr123MetalMfr123Plant1
Sup1AAA1Sup1AAA1MetalMfr123MetalMfr123Plant2
Sup1AAA1Sup1AAA1MetalMfr123MetalMfr123Plant3
Sup2AAA2Sup2AAA2WoodMfrAAAWoodMfrAAAPlant1
Sup2AAA2Sup2AAA2WoodMfrAAAWoodMfrAAAPlant2
Sup2AAA2Sup2AAA2WoodMfrBBBWoodMfrBBBPlant3
Sup3AAA3Sup3AAA3PlasticMfr11AAPlasticMfr11AAPlant1
Sup3AAA3Sup3AAA3PlasticMfr11AAPlasticMfr11AAPlant2
Sup3AAA3Sup3AAA3DifferentPlasticMfr11AADifferentPlasticMfr11AAPlant3

 

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!

Labels (1)
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

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

View solution in original post

6 Replies
jwjackso
Specialist III
Specialist III

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

I have the same issue. 

janexueer
Contributor
Contributor

Thank you. 

Stephens
Contributor II
Contributor II
Author

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
Specialist III
Specialist III

I'm running the script successfully in Qlik Sense, see images below:Capture1.PNGCapture2.PNG

Stephens
Contributor II
Contributor II
Author

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!