topic Re: Need help on Load script in QlikView

Need help on Load script

Thu, 26 May 2016 04:09:38 GMT

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!

Re: Need help on Load script

settu_periasamy — Thu, 26 May 2016 04:30:29 GMT

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;

Re: Need help on Load script

sujeetsingh — Thu, 26 May 2016 04:46:37 GMT

please come in more words

Re: Need help on Load script

Thu, 26 May 2016 12:37:25 GMT

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.