Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bwisealiahmad
Partner - Specialist
Partner - Specialist

Remove rows based on condition

Hi,

I am trying to remove some rows from a phone log. The issue is that when they call from a phone central it logs both on main number and the actual number that that they are calling from. In this case I want to remove the rows with "+4767128800" but keep the row for 8834.

When these duplicates happen the RecNr, Time, LengthOfCall and Direction is always the same.

Capture.JPG

1 Solution

Accepted Solutions
sunny_talwar

You mean to remove them or flag them? I have flagged them using this (Done it in QlikView, but Qlik Sense should be the same)

Table:

LOAD RecNo() as SNo,

id,

    caller,

    recipient,

    starttime,

    endtime,

    duration,

    direction,

    recordtype,

    Len(PurgeChar(caller, '*')) as LenCaller

FROM

[..\..\Downloads\exampledata.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If(recipient = Previous(recipient) and starttime = Previous(starttime) and endtime = Previous(endtime) and duration = Previous(duration) and direction = Previous(direction) and LenCaller > 4, 1, 0) as Flag

Resident Table

Order By recipient, starttime, endtime, duration, direction, LenCaller;

DROP Table Table;


Capture.PNG

View solution in original post

10 Replies
vinieme12
Champion III
Champion III

do you have a separate field that specifies which line was actually used to make the call?

of you could just use

if(len(CallerNR)=4 , CallerNR)  and suppress null values on dimension

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Hi,

No. That is the statement / condition I am trying to create so that I can flag them and remove them based on that condition.

That would have worked, but the issue is that soemtimes they do use the longer number, but then there isn't any other rows with the exact same timing, lenght of call and receiving number. Their logging is creating  a duplicate row with the main number and outgoing number and I am trying to get rid of these duplicates.

sunny_talwar

So you are looking to flag them in the script?

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

Yes.

sunny_talwar

May be like this

LOAD CallerNr,

    RecNr,

    Time,

    LengthOfCall,

    Direction,

    If(RecNr = Previous(RecNr) and Time = Previous(Time) and LengthOfCall = Previous(LengthOfCall) and Direction = Previous(Direction) and Len(CallerNr) > 4, 1, 0) as Flag

Resident .....

Order By RecNr, Time, LengthOfCall, Direction, CallerNr;

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

That would have worked, but it looks like they don't always come in the same order. Long and then short or vice versa. This might be trickier than expected.

sunny_talwar

I added a Order by statement.... even that won't put them together?

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

No...still didn't turn out correct. Added an excel with some demo data. The red marked ones are the ones to be removed here.

sunny_talwar

You mean to remove them or flag them? I have flagged them using this (Done it in QlikView, but Qlik Sense should be the same)

Table:

LOAD RecNo() as SNo,

id,

    caller,

    recipient,

    starttime,

    endtime,

    duration,

    direction,

    recordtype,

    Len(PurgeChar(caller, '*')) as LenCaller

FROM

[..\..\Downloads\exampledata.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If(recipient = Previous(recipient) and starttime = Previous(starttime) and endtime = Previous(endtime) and duration = Previous(duration) and direction = Previous(direction) and LenCaller > 4, 1, 0) as Flag

Resident Table

Order By recipient, starttime, endtime, duration, direction, LenCaller;

DROP Table Table;


Capture.PNG