Avoid duplicates in product matrix

[maxsheva]

Hi,

Please help to avoid duplicates in product matrix.

Below is data sample. Attachment has clarification.

Data:

LOAD * Inline [

%SalesID, ProductID

1, 10002

1, 10003

1, 10004

2, 10001

2, 10002

2, 10002

2, 10005

2, 10007

3, 10001

3, 10009

];

ProductMatrix:

LOAD    %SalesID,

ProductID as ProductID1

Resident Data;

Left Join(ProductMatrix)

LOAD

    %SalesID,

    ProductID as ProductID2

Resident Data;

Drop Table Data;

[maxsheva]

stalwar1, gwassenaar, jagan, tresescoloveisfail‌‌, mrkachhiaimp

Qlik Guru, apologies may take your time but today I need your experience to help in my issue.

Would you please have a look on my post above?

I am trying to provide all possible details.

Unfortunately I cannot load qvw from my workplace but have inserted dummy data in my first post.

Any help would be greatly appreciated!

[MK_QSL · Accepted solution]

There must be a better way but you can use below time being.

Data:
LOAD * Inline [
%SalesID, ProductID
1, 10002
1, 10003
1, 10004
2, 10001
2, 10002
2, 10002
2, 10005
2, 10007
3, 10001
3, 10009
];

ProductMatrix:
LOAD    
 %SalesID,
 ProductID as ProductID1
Resident Data;

Left Join(ProductMatrix)

LOAD
    %SalesID,
    ProductID as ProductID2
Resident Data;

Drop Table Data;

NoConcatenate
Temp:
Load *, RangeMin(ProductID1,ProductID2) & '-' & RangeMax(ProductID1,ProductID2) as Key Resident ProductMatrix Where ProductID1 <> ProductID2;

Drop Table ProductMatrix;

NoConcatenate
Temp2:
Load *,If(Key=Previous(Key),'Remove') as Flag Resident Temp Order By Key;
Drop Table Temp;

NoConcatenate
Final:
Load %SalesID,ProductID1,ProductID2 Resident Temp2 Where Flag <> 'Remove';

Drop Table Temp2;

[sunny_talwar]

Or may be just this

Data:

LOAD * INLINE [

    %SalesID, ProductID

    1, 10002

    1, 10003

    1, 10004

    2, 10001

    2, 10002

    2, 10002

    2, 10005

    2, 10007

    3, 10001

    3, 10009

];

ProductMatrix:

LOAD %SalesID,

ProductID as ProductID1

Resident Data;

Left Join(ProductMatrix)

LOAD %SalesID,

ProductID as ProductID2

Resident Data;

FinalTable:

NoConcatenate

LOAD *

Resident ProductMatrix

Where ProductID1 < ProductID2;

Drop Table Data, ProductMatrix;

[maxsheva]

Thank you Manish, Sunny for quick response.

I believe both solutions can provide correct result and avoid duplicates.

Just let me take a pause in order to test it from my end.

I will return back soon.

[effinty2112]

Hi Max,

you could add this you the end of your script:

NoConcatenate

NewProductMatrix:

LOAD

*,

Autonumber(RangeMin(ProductID1,ProductID2)& '|' & RangeMax(ProductID1,ProductID2)) as ComboID

Resident ProductMatrix Where not Exists(ComboID,Autonumber(RangeMin(ProductID1,ProductID2)& '|' & RangeMax(ProductID1,ProductID1)))

AND ProductID1<>ProductID2;

DROP Table ProductMatrix;

cheers

Andrew

[maxsheva]

All 3 solutions can be used to achieve this result.

Only found if ProductID is string then should change to RangeMinString and RangeMaxString

Thank you very much for help!