topic Remove rows based on condition in App Development

Remove rows based on condition

bwisealiahmad — Mon, 23 Oct 2017 10:35:42 GMT

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.

Re: Remove rows based on condition

vinieme12 — Mon, 23 Oct 2017 10:42:16 GMT

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

Re: Remove rows based on condition

bwisealiahmad — Mon, 23 Oct 2017 10:45:52 GMT

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.

Re: Remove rows based on condition

sunny_talwar — Mon, 23 Oct 2017 11:32:58 GMT

So you are looking to flag them in the script?

Re: Remove rows based on condition

bwisealiahmad — Mon, 23 Oct 2017 11:37:55 GMT

Yes.

Re: Remove rows based on condition

sunny_talwar — Mon, 23 Oct 2017 11:45:40 GMT

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;

Re: Remove rows based on condition

bwisealiahmad — Mon, 23 Oct 2017 12:02:18 GMT

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.

Re: Remove rows based on condition

sunny_talwar — Mon, 23 Oct 2017 12:05:30 GMT

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

Re: Remove rows based on condition

bwisealiahmad — Mon, 23 Oct 2017 12:50:38 GMT

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.

Re: Remove rows based on condition

sunny_talwar — Mon, 23 Oct 2017 13:02:18 GMT

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;

Re: Remove rows based on condition

bwisealiahmad — Mon, 23 Oct 2017 20:33:05 GMT

Ended up doing it a different way, but this also is a good solution.

Thanks like always Sunny. Great reply