Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AmCh
Creator
Creator

How do I script a compatibility matrix?

Hi all,

I have the following table as compatibility matrix:

  A1 A2 B1 B2
A1 0 1 0 1
A2 1 0 1 0
B1 0 1 0 1
B2 1 0 1 0


They are however elements of fields, as shown in the following tables:

A
A1
A2

 

B
B1
B2

 



I want to generate a table with all possible combination.

How is it possible?

Thanks in advance

Regards,
Amor

2 Solutions

Accepted Solutions
Saravanan_Desingh

Try this,

tab1:
LOAD * INLINE [
    F1, A1, A2, B1, B2
    A1, 0, 0, 0, 1
    A2, 0, 0, 1, 0
    B1, 0, 1, 0, 0
    B2, 1, 0, 0, 0
];

tab2:
CrossTable(Key, Value)
LOAD * Resident tab1;

tab3:
LOAD F1, Key As K1 Resident tab2
Where Value;

tab4:
LOAD F1 As A, K1 As B Resident tab3
Where F1 Like 'A*';

Concatenate(tab4)
LOAD F1 As B, K1 As A Resident tab3
Where F1 Like 'B*';

Drop Table tab1, tab2, tab3;

commQV31.png

View solution in original post

MarcoWedel

Hi,

maybe like this?:

 

MarcoWedel_0-1649503062216.png

 

MarcoWedel_1-1649503076028.png

 

tabTemp:
CrossTable (B,IsCompatible)
LOAD * FROM [https://community.qlik.com/t5/QlikView-App-Dev/Compatibility-matrix/m-p/1916516] (html, codepage is 1252, embedded labels, table is @4);

tabCompatibility:
LOAD F1 as A, B     
Resident tabTemp
Where F1 like 'A*' and B like 'B*' and IsCompatible;

DROP Table tabTemp;

 

hope this helps

Marco

View solution in original post

7 Replies
MarcoWedel

Are you looking for a script solution?

AmCh
Creator
Creator
Author

Yes, exactly!

MarcoWedel

maybe like this?

MarcoWedel_0-1647024787637.png

tabA:
LOAD A
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Compatibility-matrix/m-p/1903972] (html, codepage is 1252, embedded labels, table is @2);

tabB:
LOAD B
FROM [https://community.qlik.com/t5/QlikView-App-Dev/Compatibility-matrix/m-p/1903972] (html, codepage is 1252, embedded labels, table is @3);

tabAB:
LOAD A as Value1
Resident tabA;

tabAB:
LOAD B as Value1
Resident tabB;

Join (tabAB)
LOAD Value1 as Value2
Resident tabAB;

 

 

 

 

AmCh
Creator
Creator
Author

Hi MarcoWendel,
thank you for your reply.
I should reformulate my question.
I habe this compatiblity matrix as Input from excel sheet:

 

A1

A2

B1

B2

A1

0

0

0

1

A2

0

0

1

0

B1

0

1

0

0

B2

1

0

0

0

 

A1 and A2 are elements from Field A

B1 and B2 are element form Field B
I want to generate the following table:

A

B

A1

B2

A2

B1


Could you help me with it?

Thanks in advance.

Amor

Saravanan_Desingh

Try this,

tab1:
LOAD * INLINE [
    F1, A1, A2, B1, B2
    A1, 0, 0, 0, 1
    A2, 0, 0, 1, 0
    B1, 0, 1, 0, 0
    B2, 1, 0, 0, 0
];

tab2:
CrossTable(Key, Value)
LOAD * Resident tab1;

tab3:
LOAD F1, Key As K1 Resident tab2
Where Value;

tab4:
LOAD F1 As A, K1 As B Resident tab3
Where F1 Like 'A*';

Concatenate(tab4)
LOAD F1 As B, K1 As A Resident tab3
Where F1 Like 'B*';

Drop Table tab1, tab2, tab3;

commQV31.png

MarcoWedel

Hi,

maybe like this?:

 

MarcoWedel_0-1649503062216.png

 

MarcoWedel_1-1649503076028.png

 

tabTemp:
CrossTable (B,IsCompatible)
LOAD * FROM [https://community.qlik.com/t5/QlikView-App-Dev/Compatibility-matrix/m-p/1916516] (html, codepage is 1252, embedded labels, table is @4);

tabCompatibility:
LOAD F1 as A, B     
Resident tabTemp
Where F1 like 'A*' and B like 'B*' and IsCompatible;

DROP Table tabTemp;

 

hope this helps

Marco

Hill69
Contributor II
Contributor II


@MarcoWedel wrote: MyMerrill

Hi,

maybe like this?:

 

MarcoWedel_0-1649503062216.png

 

MarcoWedel_1-1649503076028.png

 

tabTemp:
CrossTable (B,IsCompatible)
LOAD * FROM [https://community.qlik.com/t5/QlikView-App-Dev/Compatibility-matrix/m-p/1916516] (html, codepage is 1252, embedded labels, table is @4);

tabCompatibility:
LOAD F1 as A, B     
Resident tabTemp
Where F1 like 'A*' and B like 'B*' and IsCompatible;

DROP Table tabTemp;

 

hope this helps

Marco


Amazing Script, Thank you for sharing!