topic Find ids repeated within 24 hours in QlikView

Find ids repeated within 24 hours

Tue, 06 Jan 2015 12:17:57 GMT

Hi guys,

I'm struggling with a challenging task.

I have a simple table

Code	Timestamp	other attributes
1000	10.10.10 10:10:10	...
1000	10.10.10 20:20:20	...
1000	11.10.10 10:20:10	...
2000	10.10.10 05:10:10	...
2000	10.10.10 10:10:10	...
2000	12.10.10 10:10:10	...

I want to find all the code-timestamp pairs where the code repeats itself within 24 hours.

So, eventually, my resulting table would look something like this:

Code	Timestamp
1000	10.10.10 10:10:10
1000	10.10.10 20:20:20
2000	10.10.10 05:10:10

Any ideas or hints are welcome.

Thanks.

Re: Find ids repeated within 24 hours

Tue, 06 Jan 2015 12:27:38 GMT

Hi,

try this or similar in your load script (not tested)

first sort on Code/Timestamp

load * where Diff_Hours <=24;

load

if( Previous(Code)=Code, TimeStamp-previous(TimeStamp)*24 ) as diff_hours,

if( Previous(Code)=Code, previous(Code)) as Prev_Code,

f( Previous(Code)=Code, previous(TimeStamp)) as Prev_TimeStamp

resident Table_Input;

hth

Re: Find ids repeated within 24 hours

Anonymous — Tue, 06 Jan 2015 12:32:11 GMT

First of all are you able to load your TIMESTAMP column in proper date time format . If yes share your sample qvw.

Re: Find ids repeated within 24 hours

agomes1971 — Tue, 06 Jan 2015 12:33:58 GMT

Hi,

in SQL

select Code, Timestamp, count(*) from yourtable

group by Code, Timestamp

having count(*)>1

in qlikview (but not certain)

--This is a condition only for unique data to appear

=if(aggr(NODISTINCT count(Code,TimeStamp),Code,TimeStamp)>1,1,0)

Another good thread: Find duplicate records

Regards

André Gomes

Re: Find ids repeated within 24 hours

anbu1984 — Tue, 06 Jan 2015 13:17:16 GMT

Temp:

Load *,Left(Timestamp,8) As Dt;

Load Code,Date#(Timestamp,'DD.MM.YY hh:mm:ss') As Timestamp Inline [

Code,Timestamp

1000,10.10.10 10:10:10

1000,10.10.10 20:20:20

1000,11.10.10 10:20:10

2000,10.10.10 05:10:10

2000,10.10.10 10:10:10

2000,12.10.10 10:10:10 ];

Final:

Load * Where Cnt > 1;

LOAD Code,Dt,Sum(1) As Cnt Resident Temp Group By Code,Dt;

Inner Join(Final)

Load Code,Dt,Timestamp Resident Temp;

Drop table Temp;

Re: Find ids repeated within 24 hours

Tue, 06 Jan 2015 14:20:18 GMT

I solved the problem in the load script.

I wonder if it was possible to solve it just on the expression level in the chart.

Currently, what I do is:

1) load data from file

2) sort the data by "code, timestamp desc"

3) add fields

previous (code) as next_code,
previous(timestamp) as next_recdate

4) end up with this data table:

5) create a chart with code and recdate as dimensions and this expression:

=if(code = next_code and next_recdate - recdate <= 1,1,0)