Remove rows based on condition

[bwisealiahmad]

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.

[vinieme12]

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

[bwisealiahmad]

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]

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]

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]

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 · Accepted solution]

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;