Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

bwisealiahmad
Valued Contributor

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

Re: Remove rows based on condition

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

10 Replies
vinieme12
Esteemed Contributor II

Re: Remove rows based on condition

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
Valued Contributor

Re: Remove rows based on condition

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.

MVP
MVP

Re: Remove rows based on condition

So you are looking to flag them in the script?

bwisealiahmad
Valued Contributor

Re: Remove rows based on condition

Yes.

MVP
MVP

Re: Remove rows based on condition

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
Valued Contributor

Re: Remove rows based on condition

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.

MVP
MVP

Re: Remove rows based on condition

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

bwisealiahmad
Valued Contributor

Re: Remove rows based on condition

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.

MVP
MVP

Re: Remove rows based on condition

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