Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all,
I have a logs table so IDs will be duplicated i want to remove all IDs where 'cart' exist
Sample Data:
for example as u can see 5304 is duplicated
if one of them contains stepname = 'cart' remove all 3 ids from table not just the one containing the cart
how can i achieve this in qlik?
one solution :
Data:
LOAD * INLINE [
ID, StepName, Action_date, WF_TASK_NAME
5304, send, 3/17/2019, Exemptionloan
5304, cart, 4/16/2019, Exemptionloan
5304, reassign, 05/02/2019, Exemptionloan
5321, cart, 2/24/2020, Exemptionloan
5408, reassign, 2/25/2020, Exemptionloan
];
Tmp:
load IDtmp where ct1>1 and wildmatch( ct2, '*cart*')>0;
load ID as IDtmp,count(StepName) as ct1,concat(StepName,'') as ct2 resident Data group by ID;
Final:
noconcatenate
load * resident Data where not Exists(IDtmp,ID);
drop table Data,Tmp;
One solution is.
tab1:
LOAD * INLINE [
ID, StepName, Action_date, WF_TASK_NAME
5304, send, 3/17/2019, Exemptionloan
5304, cart, 4/16/2019, Exemptionloan
5304, reassign, 05/02/2019, Exemptionloan
5321, cart, 2/24/2020, Exemptionloan
5408, reassign, 2/25/2020, Exemptionloan
];
Left Join(tab1)
LOAD ID, If(Count(ID)>1 And Index(Concat(DISTINCT StepName),'cart'), 'Y', 'N') As Flag
Resident tab1
Group By ID;