Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
anwarbham
Contributor III
Contributor III

remove row with duplicate

Hi i have a simple table that i need to remove e a row based on a filter

where %Call_Key is the same  as well as the date and if the Flag is empty, it should be removed.

iIn Sql i can do a simple delete were count (%Call_Key) > 1 and flag is empty .

can i do something similar in Qlik with a resident table.

 

%Calendar_Key%User_Key%Cycle_Plan_KeyWeekNum%Account_Key%Call_KeyflagMonthSource
23/10/20200050W000007OJQaQAOa3G0W000004ZISnUAO430010W00002EYeDAQA1a040W00001wRJK4QAO110Actual
26/10/20200050W000007OJQaQAOa3G0W000004ZISnUAO440010W00002EYeDAQA1a040W00001wRo3TQAS 10Actual
26/10/20200050W000007OJQaQAOa3G0W000004ZISnUAO440010W00002EYeDAQA1a040W00001wRo3TQAS210Actual
09/11/20200050W000007OJQaQAOa3G0W000004ZISnUAO460010W00002EYeDAQA1a040W00001wTndpQAC 11Actual
18/11/20200050W000007OJQaQAOa3G0W000004ZISnUAO470010W00002EYeDAQA1a040W00001whh1jQAA 11Actual
02/12/20200050W000007OJQaQAOa3G0W000004ZISnUAO490010W00002EYeDAQA1a040W00001wjeVGQAY 12Actual
09/12/20200050W000007OJQaQAOa3G0W000004ZISnUAO500010W00002EYeDAQA1a040W00001wkaCMQAY 12Actual
11/12/20200050W000007OJQaQAOa3G0W000004ZISnUAO500010W00002EYeDAQA1a040W00001wkfyVQAQ 12Actual
Labels (2)
1 Solution

Accepted Solutions
QFabian
Specialist III
Specialist III

Hi @anwarbham , please check if this is an acceptable solution for you.

It's about assign the same flag to the equal  %Calendar_Key and %Call_Key, and then select distincts ones

Creating the Id in descendent order allows me to use previous function.

 

DataX:
LOAD * INLINE [
%Calendar_Key, %User_Key, %Cycle_Plan_Key, WeekNum, %Account_Key, %Call_Key, flag, Month, Source
23/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 43, 0010W00002EYeDAQA1, a040W00001wRJK4QAO, 1, 10, Actual
26/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 44, 0010W00002EYeDAQA1, a040W00001wRo3TQAS,, 10, Actual
26/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 44, 0010W00002EYeDAQA1, a040W00001wRo3TQAS, 2, 10, Actual
09/11/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 46, 0010W00002EYeDAQA1, a040W00001wTndpQAC,, 11, Actual
18/11/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 47, 0010W00002EYeDAQA1, a040W00001whh1jQAA,, 11, Actual
02/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 49, 0010W00002EYeDAQA1, a040W00001wjeVGQAY,, 12, Actual
09/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 50, 0010W00002EYeDAQA1, a040W00001wkaCMQAY,, 12, Actual
11/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 50, 0010W00002EYeDAQA1, a040W00001wkfyVQAQ,, 12, Actual
];

DataX2:
Load
rowno() as Id,
*
Resident DataX;
drop table DataX;

DataX3:
Load
recno() as Id2,
*

Resident DataX2
order by
Id desc;
drop table DataX2;

Data:
LOad distinct
%Calendar_Key,
%User_Key,
%Cycle_Plan_Key,
WeekNum,
%Account_Key,
%Call_Key,
if(%Call_Key = previous(%Call_Key) and %Calendar_Key = previous(%Calendar_Key), previous(flag), flag) as flag,
Month,
Source
Resident DataX3
order by Id;

drop table DataX3;

QFabian

View solution in original post

5 Replies
QFabian
Specialist III
Specialist III

Hi @anwarbham , please check if this is an acceptable solution for you.

It's about assign the same flag to the equal  %Calendar_Key and %Call_Key, and then select distincts ones

Creating the Id in descendent order allows me to use previous function.

 

DataX:
LOAD * INLINE [
%Calendar_Key, %User_Key, %Cycle_Plan_Key, WeekNum, %Account_Key, %Call_Key, flag, Month, Source
23/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 43, 0010W00002EYeDAQA1, a040W00001wRJK4QAO, 1, 10, Actual
26/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 44, 0010W00002EYeDAQA1, a040W00001wRo3TQAS,, 10, Actual
26/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 44, 0010W00002EYeDAQA1, a040W00001wRo3TQAS, 2, 10, Actual
09/11/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 46, 0010W00002EYeDAQA1, a040W00001wTndpQAC,, 11, Actual
18/11/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 47, 0010W00002EYeDAQA1, a040W00001whh1jQAA,, 11, Actual
02/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 49, 0010W00002EYeDAQA1, a040W00001wjeVGQAY,, 12, Actual
09/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 50, 0010W00002EYeDAQA1, a040W00001wkaCMQAY,, 12, Actual
11/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 50, 0010W00002EYeDAQA1, a040W00001wkfyVQAQ,, 12, Actual
];

DataX2:
Load
rowno() as Id,
*
Resident DataX;
drop table DataX;

DataX3:
Load
recno() as Id2,
*

Resident DataX2
order by
Id desc;
drop table DataX2;

Data:
LOad distinct
%Calendar_Key,
%User_Key,
%Cycle_Plan_Key,
WeekNum,
%Account_Key,
%Call_Key,
if(%Call_Key = previous(%Call_Key) and %Calendar_Key = previous(%Calendar_Key), previous(flag), flag) as flag,
Month,
Source
Resident DataX3
order by Id;

drop table DataX3;

QFabian
Saravanan_Desingh

Try this,

tab1:
LOAD *
Where Not Exists(%Calendar_Key);
LOAD * INLINE [
%Calendar_Key, %User_Key, %Cycle_Plan_Key, WeekNum, %Account_Key, %Call_Key, flag, Month, Source
23/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 43, 0010W00002EYeDAQA1, a040W00001wRJK4QAO, 1, 10, Actual
26/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 44, 0010W00002EYeDAQA1, a040W00001wRo3TQAS,, 10, Actual
26/10/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 44, 0010W00002EYeDAQA1, a040W00001wRo3TQAS, 2, 10, Actual
09/11/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 46, 0010W00002EYeDAQA1, a040W00001wTndpQAC,, 11, Actual
18/11/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 47, 0010W00002EYeDAQA1, a040W00001whh1jQAA,, 11, Actual
02/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 49, 0010W00002EYeDAQA1, a040W00001wjeVGQAY,, 12, Actual
09/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 50, 0010W00002EYeDAQA1, a040W00001wkaCMQAY,, 12, Actual
11/12/2020, 0050W000007OJQaQAO, a3G0W000004ZISnUAO, 50, 0010W00002EYeDAQA1, a040W00001wkfyVQAQ,, 12, Actual
];
Saravanan_Desingh

Output:

commQV16.PNG

anwarbham
Contributor III
Contributor III
Author

Hi Almost i get the flag with  value one i need to retain the flag with value 2  

anwarbham
Contributor III
Contributor III
Author

Thanks @QFabian 

i got the result i just had to set the order on the last table 

 

LOad distinct
%Calendar_Key,
%User_Key,
%Cycle_Plan_Key,
WeekNum,
%Account_Key,
%Call_Key,
if(%Call_Key = previous(%Call_Key) and %Calendar_Key = previous(%Calendar_Key), previous(flag),flag) as flag,
Month,
Source
Resident DataX3
order by WeekNum;