Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help on Load script

Hello Everyone,

I need to load a list similar like this SQL:

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

 

IDResult Need
A.A.A.A.A.A.A.AA.A.A.A.A.A.A.A
B.B.B.B.B.B.B.BB.B.B.B.B.B.B.B
A.C.A.A.A.A.A.BA.C.A.A.A.A.A.B
A.D.A.A.A.AA.D.A.A.A.A
A.A.A.A.A.B.B
A.A.A.A.B
B.B.B.B.B.A.A.AB.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.BA.B.B.B.B.B

Can you help?

Thank you very much in advance!

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

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;


Capture.JPG

View solution in original post

3 Replies
settu_periasamy
Master III
Master III

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;


Capture.JPG

sujeetsingh
Master III
Master III

please come in more words

Not applicable
Author

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.