Open Items on load

[#1]

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

[#2]

Hi Anne,

Could you profile a small example that could help us have a better understanding of the logic?

[#3]

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

1. for Last Curr_Txn_Date if [State_Code]  = RDY=> OPEN
2. if [State_Code]  = CMP or SUS after Last Curr_Txn_Date=> OPEN

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?

[#4]

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

[maxgro]

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

[#6]

Hi Massimo

Thanks for your solution - just checking my logic now

Really appreciated

A

[#7]

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