topic Re: remove row with duplicate in QlikView

remove row with duplicate

anwarbham — Thu, 25 Feb 2021 00:07:50 GMT

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_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

Re: remove row with duplicate

QFabian — Thu, 25 Feb 2021 03:20:01 GMT

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;

Re: remove row with duplicate

Saravanan_Desingh — Thu, 25 Feb 2021 03:31:05 GMT

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 ];

Re: remove row with duplicate

Saravanan_Desingh — Thu, 25 Feb 2021 03:31:36 GMT

Output:

Re: remove row with duplicate

anwarbham — Thu, 25 Feb 2021 11:07:08 GMT

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

Re: remove row with duplicate

anwarbham — Thu, 25 Feb 2021 11:36:09 GMT

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;