Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
thanks for the reply bro but the above code is going on an on