topic Re: Open Items on load in QlikView

Open Items on load

Fri, 07 Mar 2014 17:00:29 GMT

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

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

Re: Open Items on load

Fri, 07 Mar 2014 19:19:52 GMT

Hi Anne,

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

Re: Open Items on load

Fri, 07 Mar 2014 19:32:33 GMT

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

for Last Curr_Txn_Date if [State_Code] = RDY=> OPEN
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?

Re: Open Items on load

Sat, 08 Mar 2014 12:18:41 GMT

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

Re: Open Items on load

maxgro — Sat, 08 Mar 2014 14:44:54 GMT

with this script

Source:

LOAD Process_instance,

Curr_Txn_Date,

State_Code,

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

Re: Open Items on load

Mon, 10 Mar 2014 11:14:02 GMT

Hi Massimo

Thanks for your solution - just checking my logic now

Really appreciated

Re: Open Items on load

Mon, 10 Mar 2014 11:58:35 GMT

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