Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I am loading a few excel sheets into a dashbaord and I need to identify when an item is open
The following are the headers
ID
Process_instance = Application Code
Product_Id
Curr_Activity_Description = Team
Curr_Txn_Date = Timedate field
State_Code = Status
Prev_Activity_Description
Prev_Txn_Date
Actioned_User_Id
Team_Description
COUNTRDY
RESPONSIBILITY
And the logic is
if {Last [Curr_Txn_Date] AND [State_Code] = RDY }
where there is no [State_Code] = CMP or [State_Code] = SUS that has a timestamp AFTER the Last [Curr_Txn_Date]
then its equal OPEN
If anyone could help me apply this logic i'd really appreciate it.
Thanks
A
Hi Anne,
Could you profile a small example that could help us have a better understanding of the logic?
Hey Anne,
Do you mind rephrasing the logic. I dont quite follow. Also can you attach a sample file with data if possible. Just some dummy data.
so you want
You said you have multiple excel sheets. So from which sheet should Last Curr_Txn_Date be determined??
Say from Sheet 1 you find last date(Lets call it Max). In sheet 2 and sheet 3 if time is greater than Max and state code = CMP or SUS then open?
Is this the logic?
Hi Ajay
All the sheets of data are just transaction logs from a system so hold a log of the same info - a process may open on Monday and only close on Wednesday but its "Flow" will be aparent across all the days sheet between.
The below is a sample of the data
Process_instance | Curr_Txn_Date | State_Code | |
123 | 01/01/2014 10:00 | RDY | |
123 | 01/01/2014 10:30 | RDY | |
123 | 01/01/2014 11:00 | CMP | |
567 | 02/01/2014 10:00 | RDY | |
567 | 02/01/2014 10:30 | CMP | |
567 | 02/01/2014 11:00 | RDY | OPEN |
So as 123 latest date is 1/1/14 11:00 and its CMP this is not open -
But as 567 last instance is RDY it is Open -- the data is not always sorted by Process Instance though.
Any guidance would really be appreciated
Thanks
A
with this script
Source:
LOAD Process_instance,
Curr_Txn_Date,
State_Code,
F4
FROM
[http://community.qlik.com/thread/109919]
(html, codepage is 1252, embedded labels, table is @1);
Final:
NoConcatenate
LOAD
if(Process_instance<>Peek(Process_instance) and State_Code='RDY', 'OPEN') as [Calculated Field],
Process_instance,
Curr_Txn_Date,
State_Code
Resident Source
Order by
Process_instance,
Curr_Txn_Date desc
;
DROP Table Source;
result is (Calculated Field)
Process_instance | Curr_Txn_Date | State_Code | Calculated Field |
123 | 01/01/2014 10:00 | RDY | |
123 | 01/01/2014 10:30 | RDY | |
123 | 01/01/2014 11:00 | CMP | |
567 | 02/01/2014 10:00 | RDY | |
567 | 02/01/2014 10:30 | CMP | |
567 | 02/01/2014 11:00 | RDY | OPEN |
you can change the script to read from your excel or post the excel and someone can try to better helps you
Hi Massimo
Thanks for your solution - just checking my logic now
Really appreciated
A
Hi Massimo
Your code is doing as I explained - but my logic is slightly flawed - there may be times where there will be a CMP that has equal Curr_Txn_Date - where that is the case the Calculated field should not be OPEN - see sample data below- on 567
I tried to tweak your code but to no avail - could you help me please ?
Thanks
A
Process_instance | Curr_Txn_Date | State_Code | Calculated Field |
123 | 01/01/2014 10:00 | RDY | |
123 | 01/01/2014 10:30 | RDY | Open |
123 | 01/01/2014 09:00 | CMP | |
567 | 02/01/2014 10:00 | RDY | |
567 | 02/01/2014 11:00 | CMP | |
567 | 02/01/2014 11:00 | RDY |