Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
The following is an example of how my table looks like. Now i would like to hightlight out the staff who worked for X number of consecutives days.
Maybe a field to show between 21 to 23 what is the consecutive numbers of days they worked. or any other suggestion?
Entry
EntryDate | StaffName | TimeIn | Message |
---|---|---|---|
21/11/12 | John | 07:00:00 | IN |
21/11/12 | Peter | 08:10:00 | IN |
21/11/12 | John | 09:20:00 | IN |
21/11/12 | Mary | 07:20:00 | IN |
22/11/12 | John | 07:00:00 | IN |
22/11/12 | Peter | 08:00:00 | IN |
23/11/12 | John | 07:00:00 | IN |
23/11/12 | Mary | 07:00:00 | IN |
23/11/12 | Joe | 08:00:00 | IN |
23/11/12 | Mary | 09:00:00 | IN |
Result:
John : 3
Mary: 0
Peter: 2
Joe: 0
And how can i prompt user to enter the value for X. So if they click 3, John will be highlighted and 2 Peter will be highlighted.
Guidance please.
Thanks,
10e5x
Two things:
1. make sure the order by clause is order by StaffName, EntryDate, not order by EntryDate, StaffName
2. make sure EntryDate is recognized as a date. To be sure you can use date#(EntryDate,'DD/MM/YYY') as EntryDate in the load statement
Another thing, you may want to calculate the counter first using T1, then drop T1 and join Data on the second SQL load with the legal exits. You now have a couple of unnecessary joins which is kinda messy .
Entry2:
Load
EventDate,
date#(EntryDate,'DD/MM/YYYY') as EntryDate,
StaffName,PassNum,Company,TimeIn ;
SQL Select
`EVENT_D` AS EventDate,
`EVENT_D` AS EntryDate,
`PASS_M` AS StaffName,
`PASS_N` AS PassNum,
`COMPANY_M` AS Company,
Min(EVENT_T) as TimeIn
From `TACS_ACCESS_DTL`
WHERE `MESSAGE_X` Like '%Legal Access%'
group by `EVENT_D`, `PASS_M`, `PASS_N`, `COMPANY_M`;
Data:
load *,
if(rowno()=1 or StaffName <> previous(StaffName),1,
if(EntryDate-previous(EntryDate)=1, peek('Counter')+1,peek(Counter))) as Counter
Resident Entry2 order by StaffName,EntryDate;
Drop table Entry2;
join (Data)
Load
*;
SQL Select
`EVENT_D` AS EventDate,
`EVENT_D` AS ExitDate,
`PASS_M` AS StaffName,
`PASS_N` AS PassNum,
`COMPANY_M` AS Company,
Max(EVENT_T) as TimeOut
From `TACS_ACCESS_DTL`
WHERE `MESSAGE_X` Like '%Legal Exit%'
group by `EVENT_D`, `PASS_M`, `PASS_N`, `COMPANY_M`;
See attached qvw
Why qlikview do not allow me to open your attached?
Message showing:
...document created by another qlikview personal edition user...since you are using QlikView Personal edition, you may only open document created by yourself....
Should i continue with opening, the warning say if i continue i may be unable to open the documents created by my current user key
You're using the Personal Edition, so you can't open files created by others.
Create a new file and use the script below to load the data. Then add two listboxes for StaffName and Counter. If you select a value in Counter (the no. consecutive days) you'll see the matching StaffNames.
Temp:
load
date#(EntryDate,'DD-MM-YYYY') as EntryDate,
StaffName,
timestamp#(TimeIn,'hh:mm:ss') as TimeIn,
Message;
LOAD * INLINE [
EntryDate, StaffName, TimeIn, Message
21-11-2012, John, 7:00:00, IN
21-11-2012, Peter, 8:10:00, IN
21-11-2012, John, 9:20:00, IN
21-11-2012, Mary, 7:20:00, IN
22-11-2012, John, 7:00:00, IN
22-11-2012, Peter, 8:00:00, IN
23-11-2012, John, 7:00:00, IN
23-11-2012, Mary, 7:00:00, IN
23-11-2012, Joe, 8:00:00, IN
23-11-2012, Mary, 9:00:00, IN
];
Data:
load *,
if(rowno()=1 or StaffName <> previous(StaffName),1,
if(EntryDate-previous(EntryDate)=1, peek('Counter')+1,peek(Counter))) as Counter
Resident Temp order by StaffName, EntryDate;
It works i will digest your scripts and use it for my context, thanks so much. Anyway if you are free, could u help me with the previous post u just answered in my thread Not showing assoicated values, i have some doubts...
Hi,
Thanks althought it works by creatng a new document and pasting your scripts in it, but it does not work form me. When i modify your scripts to my context, the counter field appear but only with values1... Why is this so?
Btw mind explaining the last part? starting from if(rowno.....
Very difficult to uds myself and i want to learn the scriptings
Please
Appreciated,
Guoxiang
The previous and peek functions can be used to look in previous records. Previous looks in the record set you're loading from and peek looks in the table you're loading into.
If we are on the first record or we find a new Staffname we start the counter on 1
if(rowno()=1 or StaffName <> previous(StaffName),1,
If we find a new date we check if it is 1 day later
if(EntryDate-previous(EntryDate)=1,
If it is 1 day later we increase the counter by 1
peek('Counter')+1,
If it is not 1 day later we don't increase the counter, but use its current value
peek(Counter))) as Counter
I have tried various editing i still get the counter as 1. I have 3 dates, shouldnt it be 3? Can u help me with that?
You have a lot more fields, so it's possible the order isn't right yet to calculate the counters. I see in your qvw document you didn't include StaffName in the order by clause. Maybe if you add it to the order by clause things will go better.
Thanks for the reply but sorry i really dont get what u mean. add StaffName to the order by clause things will go better. i tot the staffName is already included in the order by? i try edit myself but still stuck at counter equals only 1. I look at table viewer but still cant uds what u trying to teach.... example or details please?
Or if u need, i can cut down on some of the field