Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
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.
So you are looking to flag them in the script?
Yes.
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;
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.
I added a Order by statement.... even that won't put them together?
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.
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;