Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
=if(code = next_code and next_recdate - recdate <= 1,1,0)
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
F.
First of all are you able to load your TIMESTAMP column in proper date time format . If yes share your sample qvw.
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
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;
=if(code = next_code and next_recdate - recdate <= 1,1,0)