Brainstorming, help creating a new dataset

[John5]

Not sure how to approach this problem.

I have a table with two columns, "name" and "case no".
A case no can have 1 or more people belonging to it (actually in some cases it can be an empty case no, but I don't think that makes a difference here).

From the above table I would like to create a new dataset with 3 columns, "source", "target" and "nooftimes". This table should contain all the names, and show who and how many times certain names have been together in the same cases. 

Example data:

name, case no

Dan, AB546

Sue, TY874

Steve, AB546

Brian, UI983

Glen, TT132

Jane, TY874

Sue, TT132

Jane, TT132

This is what I want to achieve. A dataset with the connections...

source, target, nooftimes

Dan, Steve, 1

Steve, Dan, 1

Sue, Jane, 2

Jane, Sue, 2

Glen, Jane, 1

Jane, Glen, 1

Glen, Sue, 1

Sue, Glen, 1

Brian, -, 0

How can this be achieved?
Any help is apreciated

[Kushal_Chawda · Accepted solution]

@John5  one way

data:
LOAD * Inline [
name, case no
Dan, AB546
Sue, TY874
Steve, AB546
Brian, UI983
Glen, TT132
Jane, TY874
Sue, TT132
Jane, TT132 ];

Left Join(data)
Load [case no],
     concat(name,'|') as Names_by_case
Resident data
Group by [case no]; 

Join(data)
Load FieldValue('name',RecNo()) as All_Names
AutoGenerate FieldValueCount('name');

T1: 
Load *,
     if(All_Names<>name and SubStringCount(Names_by_case,'|'),
    if(SubStringCount(Names_by_case,name) and SubStringCount(Names_by_case,All_Names),name),
    if(not SubStringCount(Names_by_case,'|') and Names_by_case=All_Names,name)) as Source,
    
    if(All_Names<>name and SubStringCount(Names_by_case,'|'),
    if(SubStringCount(Names_by_case,name) and SubStringCount(Names_by_case,All_Names),All_Names),
    if(not SubStringCount(Names_by_case,'|') and Names_by_case=All_Names,Null())) as Target,
    
    if(All_Names<>name and SubStringCount(Names_by_case,'|'),
    if(SubStringCount(Names_by_case,name) and SubStringCount(Names_by_case,All_Names),1),
    if(not SubStringCount(Names_by_case,'|') and Names_by_case=All_Names,1)) as Flag,
    
    if(not SubStringCount(Names_by_case,'|') and Names_by_case=All_Names,1) as Flag_0_time

Resident data;

Drop Table data;

Final:
Load Source,
     Target,
     Sum(if(Flag_0_time=1,0,Flag)) as no_of_times
Resident T1
where Flag=1
Group by Source,Target;

Drop Table T1;

[John5]

Thank you so much.😁

This was exactly the result I wanted.

Looking at your script, I don't think I would have figured this out myself.

John