Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hamzabob1
Partner - Contributor III
Partner - Contributor III

How to convert Recursive query in Qliksense?

Hi Guys,

 

Can you please help me to convert below SQL query into qliksense.

with recursive successors as(

select dt_event_key_1, dt_event_key_2

from rbm_pu7_mdt.dt_event_link

where dt_event_key_1 = 196685

 

union

select tgt.dt_event_key_1, tgt.dt_event_key_2

from rbm_pu7_mdt.dt_event_link tgt

 

inner join successors s on s.dt_event_key_2 = tgt.dt_event_key_1

)

select distinct tgt.target_layer

from successors

join rbm_pu7_mdt.dt_event tgt on successors.dt_event_key_2 = tgt.dt_event_key

where tgt.target_layer like 'ACL%' and tgt.target_layer not like 'ACL_SDM%';

Note:- I don't wanna run as its query in qliksense.

 

Many Thanks

@Anil_Babu_Samineni @jonathandienst @sunny_talwar 

Labels (1)
2 Replies
Chanty4u
MVP
MVP

Try this

 

dt_event_link:

LOAD dt_event_key_1,

     dt_event_key_2

FROM rbm_pu7_mdt.dt_event_link.qvd (qvd);

 

LOAD dt_event_key_1,

     dt_event_key_2

RESIDENT dt_event_link

WHERE dt_event_key_1 = 196685;

 

DO WHILE NOT IsNull(Peek('dt_event_key_2'));

    LEFT JOIN (dt_event_link)

    LOAD dt_event_key_1,

         dt_event_key_2

    RESIDENT dt_event_link

    WHERE dt_event_key_1 = Peek('dt_event_key_2');

LOOP;

 

 

dt_event:

LOAD DISTINCT target_layer,

              dt_event_key

FROM rbm_pu7_mdt.dt_event.qvd (qvd);

 

FinalTable:

LOAD DISTINCT target_layer

RESIDENT dt_event

WHERE target_layer LIKE 'ACL%' AND NOT WildMatch(target_layer, 'ACL_SDM%');

 

DROP TABLE dt_

event_link;

DROP TABLE dt_event;

hamzabob1
Partner - Contributor III
Partner - Contributor III
Author

thanks for the reply bro but the above code is going on an on 

hamzabob1_0-1706780137607.png