topic Re: Avoid duplicates in product matrix in QlikView

Avoid duplicates in product matrix

maxsheva — Thu, 05 Apr 2018 20:13:14 GMT

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

Re: Avoid duplicates in product matrix

maxsheva — Fri, 06 Apr 2018 10:02:16 GMT

stalwar1, gwassenaar, jagan, tresesco loveisfail‌‌, 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!

Re: Avoid duplicates in product matrix

MK_QSL — Fri, 06 Apr 2018 10:35:35 GMT

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;

Re: Avoid duplicates in product matrix

sunny_talwar — Fri, 06 Apr 2018 11:19:15 GMT

Or may be just this

Data:

LOAD * INLINE [

%SalesID, ProductID

1, 10002

1, 10003

1, 10004

2, 10001

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;

Re: Avoid duplicates in product matrix

maxsheva — Fri, 06 Apr 2018 11:29:24 GMT

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.

Re: Avoid duplicates in product matrix

effinty2112 — Fri, 06 Apr 2018 13:00:43 GMT

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

Re: Avoid duplicates in product matrix

maxsheva — Fri, 06 Apr 2018 21:45:05 GMT

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!