Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
QV_learner
Contributor II
Contributor II

Display matrix of dimensions

Hello,

I have data in the following format:

IDABCDEF
1110010
2010100
3001011
4100111
5010000
6101000

 

 

I want to display it as:

 ABC
D   
E   
F  

 

 

where the blank space will contain count IDs as:

blank space between A and D will contain count of IDs with A=1 and D=1, and so on.


I tried using inline table with  expression; but no success!
Could you please give me some direction to work on this?

 

Thanks!

1 Solution

Accepted Solutions
marcus_sommer

Just use:

t0: load * inline [
ID A B C D E F
1 1 1 0 0 1 0
2 0 1 0 1 0 0
3 0 0 1 0 1 1
4 1 0 0 1 1 1
5 0 1 0 0 0 0
6 1 0 1 0 0 0
] (txt, delimiter is \t);

t1: crosstable(cat1, val1, 1) load ID, A, B, C resident t0;
t2: crosstable(cat2, val2, 1) load ID, D, E, F resident t0;
t3: noconcatenate load * resident t1; left join(t3) load * resident t2;
drop tables t0, t1, t2;

MatrixResolutionWithJoinedCrosstable.JPG

- Marcus

 

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

I suggest that you add the expected output in the output table to help clarify your explanation.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
QV_learner
Contributor II
Contributor II
Author

 ABC
D110
E211
F101

 

for cell 00(cell DA), the expression will be: count({<A={1},D={1}>}ID)

for cell 01(cell DB), the expression will be: count({<A={1},D={1}>}ID) and so on..

 

Thanks!

marcus_sommer

Just use:

t0: load * inline [
ID A B C D E F
1 1 1 0 0 1 0
2 0 1 0 1 0 0
3 0 0 1 0 1 1
4 1 0 0 1 1 1
5 0 1 0 0 0 0
6 1 0 1 0 0 0
] (txt, delimiter is \t);

t1: crosstable(cat1, val1, 1) load ID, A, B, C resident t0;
t2: crosstable(cat2, val2, 1) load ID, D, E, F resident t0;
t3: noconcatenate load * resident t1; left join(t3) load * resident t2;
drop tables t0, t1, t2;

MatrixResolutionWithJoinedCrosstable.JPG

- Marcus

 

QV_learner
Contributor II
Contributor II
Author

Thank you Marcus for the help!