Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Connecting a key field in table A to two fields in table B

Hello Everyone,

I have a big issue. I have two tables.

Table A where I have the user ID (user ID is the key field) and the ID name.

And table B which provides information on tickets opened and in this table i have the field of:

i) the user who created the ticket (Requestor)

ii) the user who closed the ticket(user ID).

Currently the key field is connected with the user who solved the ticket.

Right now I need to build a bar chart where my Dimension is the 'ID name' and the number of tickets created by the users for the last 5 days.

My expression is as follows:

=Count({<Date={'>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))'}>} Requestor)

However, Qlikview is showing the good dimension but is counting the user ID(the user who closed the ticket).

Could you please help.

Many Thanks,

Hasvine

10 Replies
balkumarchandel
Valued Contributor II

Re: Connecting a key field in table A to two fields in table B

you can create one more column in A ,

[user ID] ,

[user ID] as Requestor


so that your both table will link with two fields

then use same expression.


Thanks

BKC

kamielrajaram
Contributor III

Re: Connecting a key field in table A to two fields in table B

Hi,

Try this.

Users:
LOAD * INLINE [
User ID, ID Name
1, a
2, b
3, c
4, d
5, e
]
;

Tbl:
LOAD * INLINE [
User ID, Ticket, Source
1, 32, Request
2, 333, Request
3, 345, Request
]
;

Concatenate
LOAD * INLINE [
User ID, Ticket, Source
4, 32, Closed
3, 333, Closed
5, 345, Closed
]
;

You expression should read : =Count({<Date={'>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))'}, Source = {'Request'}>} [User ID])

Re: Connecting a key field in table A to two fields in table B

Hi Hasvine, what QV is doing in your model is:

- You select an [User ID], so the model is filtered to that [User ID], which filters the tickets closed by that user.

- When you count the requestor, QV is counting how many tickets closed by that [User ID] was opened by someone (can be another user)

Maybe this expression can help you:

=Count({<Date={'>=$(=Date(Max(Date)-5))<=$(=Date(Max(Date)))'}, Requestor=[User ID], [User ID]>} Requestor)

Not tested, if you can upload a sample it will be nice to make some tests and give a tested answer.

Hope this helps!

Not applicable

Re: Connecting a key field in table A to two fields in table B

Hi Chandel,

Thank you its works now but however I do not get the correct answer when i use ID name as the Dimension. When I use the Requestor the answer is correct. But the requestor are numbers and these numbers are linked to name in the database.

Do you have an idea of how i can solve this issue please?

MVP
MVP

Re: Connecting a key field in table A to two fields in table B

Have a look at this posting Canonical Date

It applies to multiple date fields, but you can easily apply the same concepts to your user IDs.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
balkumarchandel
Valued Contributor II

Re: Connecting a key field in table A to two fields in table B

if can share your sample app after reducing the data . That would be helpful to ans .

Thanks

BKC

Not applicable

Re: Connecting a key field in table A to two fields in table B

I managed to do it.I created an excel sheet and with the requestors id and requestor name and i uploaded the file and finally it works.I know its not the best way to do it but I guess its the easiest :-D

Thank you all for your help.

Not applicable

Re: Connecting a key field in table A to two fields in table B

We had a similar problem, and translated to your case, the solution was to split the user table into two separate tables with different field names (RequestorID, RequestorName)  and (CloserID, CloserName) in the load script.

This article might be helpful as well:

Circular References

Not applicable

Re: Connecting a key field in table A to two fields in table B

Hi Robert,

Thanks for your help. I manage to solve the issue.

Thanks,

Hasvine

Community Browser