Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
please come in more words
Thank you very much! Appreciate your help.
Best regards.