topic Re: Getting timestamp bind on two conditions in QlikView

Getting timestamp bind on two conditions

Mon, 10 Aug 2015 06:14:13 GMT

Hello together,

iam very new to QlikView so i hope you dont laugh about my question

I want to analyse our ticketsystem and need the timestamp when tickets changed their status to closed.

There are the following necessary Tables:

jiraissue:

jiraissue_id	jiraissue_created	jiraissue_summary	jiraissue_stuff
33421	2015-03-20 19:25:34	JO JO JO	kram

changegroup

changegroup_id	jiraissue_id	changegroup_created
12345	33421	2015-03-25 10:00:00

changeitem:

changeitem_id	changegroup_id	changeitem_field	changeitem_newstring
56794	12345	status	Closed
56794	12345	resolution	done
56794	14345	status	Open

So i need the timestamp from changegroup_created Where changeitem_field = 'status' and changeitem_newstring = 'Closed'.

In MySQL i would use following query:

SELECT b.created FROM jiraissue AS a, changegroup AS b, changeitem AS c WHERE a.id = b.issueid AND c.groupid = b.id AND c.field = 'status' AND c.newstring = 'Closed'

In QlikView i tried to use following expression:

IF(MATCH(changeitem_field, 'status') AND MATCH(changeitem_newstring, 'Closed'), changegroup_created, 'versagt')

i also tried:

IF(changeitem_field='status' AND changeitem_newstring='Closed', changegroup_created, 'versagt')

But none of them worked.

Do you have any idea?

I would really appreciate your help!

Greetings

Stefan

Re: Getting timestamp bind on two conditions

avinashelite — Mon, 10 Aug 2015 09:59:03 GMT

try like this :

MaxString({<changeitem_field={'status'},changeitem_newstring={'Closed'}>}changegroup_created)

Re: Getting timestamp bind on two conditions

Gysbert_Wassenaar — Mon, 10 Aug 2015 10:01:29 GMT

Your data does not contain a timestamp that tells when a ticked was closed. There's only a creation timestamp and a changegroup creation timestamp.

Re: Getting timestamp bind on two conditions

Mon, 10 Aug 2015 10:48:32 GMT

I know that there is no direct timestamp that tells when a ticket was closed.

Let me explain:

When i make a change on a ticket, the system creates one entry in changegroup with the timestamp the change was made , the issue that was changed and the person who did this change.

What kind of changes were made within one change is written in the changeitem table.

So if i change as example the status and the assignee in one stamp, thats two changes in the changeitem table linked through the groupid with the entry in changegroup which contains the information that are equal for both changes.

So when i close a ticket one entry is made in the changegroup table containing information ticketid, timestamp and author of that change, and one or more entries are made in the changeitem table containing information what was changed. When a ticket is closed, an entry is made with field = status and newstring = closed

And i want the changegroup timestamp that refers to the entries in changeitem with that conditions.

Re: Getting timestamp bind on two conditions

Gysbert_Wassenaar — Mon, 10 Aug 2015 10:54:51 GMT

Ok, sounds easy. Load your tables like they are and they will be linked on the common field names. Then in the front end add a table box with the fields you want to see. Then simply select the value 'Closed' in changeitem_newstring and select the value 'Closed' in changeitem_field.

Re: Getting timestamp bind on two conditions

Mon, 10 Aug 2015 11:08:18 GMT

Sure, thats a way to do it.

But i want to use it in my Straight Table. I want to get the time difference with "Interval) between creation and closing of a ticket, so i need it as expression