How Can your software help me to generate this list?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Would you Please guide me (maybe Simple and fast query if there is or some fast code) to convert my CSV data file (with commas separation):

1,A,C,Z,F,G

2,G,Q,R,C,

3,Z,G,Q,

4,C,F,

5,O,P,

6,O,X,Y,J,

7,A,P,X,

I have this table with ~1,000,000 records like these 7 records that you see (**In real Database A,B,C,... are words in string**), Records 1 and 2 are common in G and C value and 2,3 and 1,3 and ...

I want to sync records if they have at least two common value like Records 1 & 2,3,4 (but record 5,6,7 haven't at least 2 shared values with others) and generate a list like this:

1 A C Z F G Q R

2 G Q R C A Z F

3 Z G Q A C F R

4 C F A Z G Q R

5 O P

6 O X Y J

7 A P X

at the end we must have 4 same records if we sort data and one others without sync:

1 A C F G Q R Z

2 A C F G Q R Z

3 A C F G Q R Z

4 A C F G Q R Z

5 O P

6 J O X Y

7 A P X

Maybe I do not use good term for my meaning, please see:

1 A C Z F G

2 G Q R C

record 1 has C and G common with Record 2 now 1 has not R and Q thus we must have 1 A C Z F G + Q and R and Record 2 has not A,Z and F thus we must have: 2 G Q R C + A,Z and F thus at the end we have:

1 A C Z F G Q R

2 G Q R C A Z F

I need all records Respectively in the queue from top to bottom.

for other example: record 4:

1,A,C,Z,F,G

2,G,Q,R,C,

3,Z,G,Q,

4,C,F,

5,O,P,

6,O,X,Y,J,

7,A,P,X,

at first we have :

1 A C Z F G Q R

2 G Q R C A Z F

3 Z G Q A C F R

4,C,F,

5,O,P,

6,O,X,Y,J,

7,A,P,X,

Now at 4 we have C and F common with 1

thus

we generate:

4 C F + a z g q r

I wrote a delphi code but it is so slow. Someone suggest me this groovy code:

def f=[:]

new File('Data.csv').readLines().each{

def items=it.split(',')

def name

items.eachWithIndex { String entry, int i ->

if(i==0){

name=entry

}

else if(entry){

if(!f[entry])

f[entry]=[]

f[entry]<<name

}

f.findAll {it.value.size()>1}

It is very fast (because of using a map file I think), but It only finds the common values.

Please give me any help or suggestion.

Thank you

topic How Can your software help me to generate this list? in QlikView

How Can your software help me to generate this list?