Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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
Creator III
Creator III

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])

rubenmarin

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
Author

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?

jonathandienst
Partner - Champion III
Partner - Champion III

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
Anonymous
Not applicable
Author

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

Thanks

BKC

Not applicable
Author

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 😄

Thank you all for your help.

Not applicable
Author

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
Author

Hi Robert,

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

Thanks,

Hasvine