Creating links between rows of same dimension

[aitmessaoudali]

hello community, i have a dimension with the names of clients separated into 5 categories (from 1 to 5 in another dimension), in my dashboard i want to show clients from all categories in separate table. How can i select one client from one table and see the related clients from other categories ?

[aitmessaoudali]

What i want actually is when when i select bb i can see dd as they have the same id, but i'm using an if condition so this won't work

[avkeep01]

I created two extra fields to do this. I don't think that is the whole solution but it gives a solution for this question.

table1:

LOAD

*

INLINE [

id, client_name, category

1,aa,A

2,bb,B

3,cc,A

2,dd,A

4,ee,B

3,ff,A

];

id:

LOAD

id,

client_name AS client_name_id

REsIDENT table1;

cat:

LOAD

category,

client_name AS client_name_cat

RESIDENT table1;

[avkeep01]

[16:31] another small update: an option to select which possibility you want to see.

I update the script reducing the two possible fields to one. I don't think I can get it better then this.

table1:

LOAD

id&'|'&category as %client_link_key,

*

INLINE [

id, client_name, category

1,aa,A

2,bb,B

3,cc,A

2,dd,A

4,ee,B

3,ff,A

];

Possible:

LOAD

id&'|' as %possible_link_key,

client_name AS client_name_possible

RESIDENT table1;

CONCATENATE (Possible) LOAD

'|'&category AS %possible_link_key,

client_name AS client_name_possible

RESIDENT table1;

link:

NOCONCATENATE LOAD

%client_link_key,

id&'|' as %possible_link_key,

'possible ID' AS type

RESIDENT table1;

CONCATENATE (link) LOAD

%client_link_key,

'|'&category AS %possible_link_key,

'possible category' AS type

RESIDENT table1;

[aitmessaoudali · Accepted solution]

hey thanks for helping, appreciate it. i think i have found a solution by loading the first table twice and imposing a condition:

table1:

LOAD * INLINE [

id, client_name, category

1,aa,A

2,bb,B

3,cc,A

2,dd,A

4,ee,B

3,ff,A

] ;

tablea:

load id,

client_name as clienta

Resident table1 where category='A';

tableb:

load id,

client_name as clientb

Resident table1 where category='B';

drop table table1;

all i have to do after is create 2 lists one with clienta and the orher with clientb, it works perfectly

[avkeep01]

Nice!

Just check  my last post just to be sure. There I have only 1 field with possible client_names left. That is easier for the end user. Also you can select the type of possibility (i.e. category or id).

[aitmessaoudali]

i checked, but i don't get the result hoped like in this picture

[avkeep01]

No problem. If your applications works than its fixed isnt it. Glad I could help.

This was my result.

[aitmessaoudali]

yes i checked ur app, but all i wanna see i bb when u select dd and i dont need to go through another filter.

Thanks for the help.