topic Re: How to get values correct which are in access database in QlikView

How to get values correct which are in access database

Thu, 16 Jan 2014 16:18:32 GMT

Hi,

Please find attached.

I need to get the number of cases closed by Person(Who) and by WorkItemtype. It is getting values less when compared to values which are in access database. Please could anyone tell me where I am doing mistake.

Thanks.

Re: How to get values correct which are in access database

Thu, 16 Jan 2014 16:24:09 GMT

Please any help

Re: How to get values correct which are in access database

jpapador — Thu, 16 Jan 2014 16:40:08 GMT

whenever I try to open the attachment Qlikview says "Failed to Load" and then closes.

Re: How to get values correct which are in access database

Thu, 16 Jan 2014 16:46:27 GMT

Please colud you find now and help me as I am really struggling with this

Re: How to get values correct which are in access database

Anonymous — Thu, 16 Jan 2014 16:50:54 GMT

Hi,

I cann't open the attached document.

Someone else ??

Paul

Re: How to get values correct which are in access database

jpapador — Thu, 16 Jan 2014 16:53:19 GMT

are you over reporting or under reporting, any chance you could post a sample of the access data?

Re: How to get values correct which are in access database

Thu, 16 Jan 2014 16:55:03 GMT

Hi,

I tried myself opening the documents it's opening for me please try once please

Re: How to get values correct which are in access database

gopalopsharma — Thu, 16 Jan 2014 16:58:57 GMT

Hello Amelia,

Are you trying some join/Keep in the script?

or There can be some blank persons/ workitem type, which might create trouble.

Re: How to get values correct which are in access database

Thu, 16 Jan 2014 17:01:43 GMT

Yes there is a join in the tables.

please help me how can i set this as this is really frustrating me please

Re: How to get values correct which are in access database

gopalopsharma — Thu, 16 Jan 2014 18:50:55 GMT

Hello Amelia,

though I could not see the data model,

but I assume you wish to join "TblClient" with "TblWorkItemtype" and "TblHistory" on workItemtype and ClientID key.

Now I think here is issue. there is one HistoryID in TblHistory which is not present in the TblClient and that is why the data in TblClient is not unique at this level.

Can you please explain what is HistoryID?
If it is the ID for each of the log in of a person,

then it should be in TblClient table.

Re: How to get values correct which are in access database

Thu, 16 Jan 2014 19:16:00 GMT

Hey Amelia,

I was able to open it. Looking at it it seems like its showing the right results.

It is working per the data model designed. You have all those null ItemStatus so they wont be considered in the count for 'Closed'

Re: How to get values correct which are in access database

Fri, 17 Jan 2014 08:53:07 GMT

Thanks. HistoryID is the ID whihc the persons history is stored so they mentioned as HistoryID but this is not in TblClient table in database side. How can I fix this issue please help me

Re: How to get values correct which are in access database

Fri, 17 Jan 2014 08:55:20 GMT

Hi,

Thanks and how can I get that NULL count also. Please help me . Is there anything I need to change the expression?

Re: How to get values correct which are in access database

Fri, 17 Jan 2014 10:01:16 GMT

Please help me as it is showing entirely different values I suppose there is a issue with expression how can it wotk will Cross table will it be useful? If so please help me how to use Cross table

Re: Re: How to get values correct which are in access database

Fri, 17 Jan 2014 15:56:26 GMT

Amelia please check the attached doc. I changed the formula for Closed (it will consider null as well).

So for example it will look like

Who	WorkItemType	Closed
Aaron McLaughlin	Original Paper Work	2
	-	7
	Total	9

The reason there is a null WorkItemType is because there are 9 client IDs which dont have a workitemtype.

The two tables of interest in your data model are History and Client. Your History has some ClientIDs that are not in Client table thats why you get all those nulls for ItemStatus and WorkitemType.

You can maybe check with your data expert and see why.

Re: How to get values correct which are in access database

Fri, 17 Jan 2014 16:43:08 GMT

Hi,

Thanks and now the values are getting doubled for each one and it is showing different for WHO and different for WORKITEMTYPE. But those should be same .

Please help me how to set this completely.

Re: Re: How to get values correct which are in access database

Fri, 17 Jan 2014 17:18:46 GMT

Oops sorry. Changed the formula.

Can you please elaborate on why WHO and WORKITEMTYPE should be same. WORKITEMTYPE is a subset of WHO so for example

WHO	WorkItemType	Closed
Ashleigh Myers	Confirmation of Payment Arrangement	1
	Modifications	1
	Review Proposal/Chairman's Report	1
	-	187
	Total	190

So Ashleigh Myers has total 190 ClientIDs of which 1 for Confirmation of Payment Arrangement, Modifications, Review Proposal/Chairman's Report and 187 with no WorkItemType assigned.

If you still think its incorrect can you post the original Access data for this one Person.What do you think should be the result. We will use this person for our testing to make things easier.

Thanks