topic Creating Identifier or grouping the records that are Similar or Shuffled in App Development

Creating Identifier or grouping the records that are Similar or Shuffled

RC_121985 — Fri, 15 Nov 2024 20:57:15 GMT

Hello Experts,

Got held back by strange challenge. I have used strange subject above. But will use simple example to simplify.

FIELD1

A|B|C|D

B|C|D|A

D|C|B|A

A|B

B|A

D|C

A|C|B|D

Looking for the Output as below any one FIELD2 or FIELD3 or FIELD4.

FIELD1	FIELD2	FIELD2	FIELD3
A\|B\|C\|D	A\|B\|C\|D	1	ABCD
B\|C\|D\|A	A\|B\|C\|D	1	ABCD
D\|C\|B\|A	A\|B\|C\|D	1	ABCD
A\|B	A\|B	2	AB
B\|A	A\|B	2	AB
C	C	3	C
D	D	4	D
D\|C	D\|C	5	DC
A\|C\|B\|D	A\|B\|C\|D	1	ABCD

Any one of the result is fine. I just want to group similar values that might have shuffled within.

Thanks

Re: Creating Identifier or grouping the records that are Similar or Shuffled

Sayed_Mannan — Fri, 31 May 2024 11:27:35 GMT

here is my solution,

Test:
Load * INLINE [
FIELD1
A|B|C|D
B|C|D|A
D|C|B|A
A|B
B|A
C
D
D|C
A|C|B|D
];

t:
LOAD
FIELD1,
SubField(FIELD1, '|') as SubField
RESIDENT Test;

TempTable:
LOAD
FIELD1,
SubField as SortedSubField
RESIDENT t
ORDER BY FIELD1, SubField;

NoConcatenate
YourTable:
LOAD
FIELD1,
Concat(SortedSubField, '|') as SortedField
RESIDENT TempTable
GROUP BY FIELD1;

DROP TABLES TempTable,t,Test;

// Drop Table Test;

EXIT SCRIPT;

if is works for you, mark this as a solution.

Re: Creating Identifier or grouping the records that are Similar or Shuffled

Clement15 — Fri, 31 May 2024 12:11:27 GMT

Hello, this answers your problem

TempTable:

load

RowNo() as IDTEMP,

FIELD1,

SubField(FIELD1,'|',1) as F1,

SubField(FIELD1,'|',2) as F2,

SubField(FIELD1,'|',3) as F3,

SubField(FIELD1,'|',4) as F4

inline [

FIELD1

A|B|C|D

B|C|D|A

D|C|B|A

A|B

B|A

D|C

A|C|B|D

];

TransposeTable:

LOAD

IDTEMP,

FIELD1,

F1 as FIELD2

RESIDENT TempTable;

CONCATENATE(TransposeTable)

LOAD

IDTEMP,

FIELD1,

F2 as FIELD2

RESIDENT TempTable;

CONCATENATE(TransposeTable)

LOAD

IDTEMP,

FIELD1,

F3 as FIELD2

RESIDENT TempTable;

CONCATENATE(TransposeTable)

LOAD

IDTEMP,

FIELD1,

F4 as FIELD2

RESIDENT TempTable;

DROP TABLE TempTable;

TEST2:

load *,

AutoNumber(FIELD4) as FIELD3,

Replace(FIELD4,' ','|') as FIELD2;

load

FIELD1,

RTrim(chr(min(ord(FIELD2),1)) & ' '& chr(min(ord(FIELD2),2)) & ' '& chr(min(ord(FIELD2),3)) & ' '& chr(min(ord(FIELD2),4))) as FIELD4

resident TransposeTable

group by IDTEMP,FIELD1

;

DROP table TransposeTable;

Re: Creating Identifier or grouping the records that are Similar or Shuffled

RC_121985 — Sat, 01 Jun 2024 04:28:38 GMT

Thank You @Sayed_Mannan for the solution. During the testing of your query, I found it don't work when there is space in between the text. It works good for single character separated by "|".

So I tested with below sample.

Test:
Load * INLINE [
FIELD1
A B|C D,
E F|G,
C D,
A B|E F|G|H I|C D,
A B|H I|C D|E F|G,
A B|E F|C D|G|H I,
A B|E F|G|H I|C D,
A B
];

Result was as below;

To handle this, i used Distinct in

NoConcatenate
YourTable:
LOAD
FIELD1,
Concat(Distinct SortedSubField, '|') as SortedField
RESIDENT TempTable
GROUP BY FIELD1;

I see the result is matching to my expectation.

Thank you very much for your Solution. I accept this as solution 🙂