topic Brainstorming, help creating a new dataset in App Development

Brainstorming, help creating a new dataset

John5 — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Brainstorming, help creating a new dataset

Kushal_Chawda — Mon, 16 Aug 2021 23:43:37 GMT

@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;

Re: Brainstorming, help creating a new dataset

John5 — Tue, 17 Aug 2021 08:30:35 GMT

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