Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
maxsheva
Creator II
Creator II

Avoid duplicates in product matrix

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;

ProductMatrix.jpg


1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

6 Replies
maxsheva
Creator II
Creator II
Author

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

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;

Capture.PNG

maxsheva
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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!