Need help on Load script

[#1]

Hello Everyone,

I need to load a list similar like this SQL:

Max(Len(ID) for substring(ID, 1,3))

ID	Result Need
A.A.A.A.A.A.A.A	A.A.A.A.A.A.A.A
B.B.B.B.B.B.B.B	B.B.B.B.B.B.B.B
A.C.A.A.A.A.A.B	A.C.A.A.A.A.A.B
A.D.A.A.A.A	A.D.A.A.A.A
A.A.A.A.A.B.B
A.A.A.A.B
B.B.B.B.B.A.A.A	B.B.B.B.B.A.A.A
B.B.B.B.B
B.B.B.B.B.A.A
B.B.B.B.A.A
B.B.B.A.B.A.A.A
A.B.B.B.B.B	A.B.B.B.B.B

Can you help?

Thank you very much in advance!

[settu_periasamy · Accepted solution]

Hi ,

Could you give more details about your expected result?

How do you arrive the "Result Need" Column?

Updated;

May be try this (based on my assumed logic)

T1:
LOAD ID,Len(ID) as Len,Mid(ID,1,5) as Mid, Mid(ID,1,5)&Len(ID) as Key;
LOAD * INLINE [
ID
A.A.A.A.A.A.A.A
B.B.B.B.B.B.B.B
A.C.A.A.A.A.A.B
A.D.A.A.A.A
A.A.A.A.A.B.B
A.A.A.A.B
B.B.B.B.B.A.A.A
B.B.B.B.B
B.B.B.B.B.A.A
B.B.B.B.A.A
B.B.B.A.B.A.A.A
A.B.B.B.B.B
];

Left Join(T1)
LOAD Max(Len) as Len,Mid,1 as Flag Resident T1 Group by Mid;

NoConcatenate
Final:
LOAD ID,if(Flag=1,ID,'') as [Result] Resident T1;

DROP Table T1;

[sujeetsingh]

please come in more words

[#4]

Thank you very much - settu_periasamy! Your solution is so smart. It just what I need.

Thank you very much! Appreciate your help.

Best regards.