topic Re: Calculate/Expression from a specific table loaded into Qlikview in QlikView

Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Tue, 03 Dec 2013 20:14:49 GMT

Hello,

I have two tables, and I am using Email as the key field to connect the two. I'd like to make a bar chart to count Emails from Table2 only. But since these two tables are connected by "Email", I was not able to do it successfully.

Some description for Table2 Email field, The same email may occur 0, 1 or more than 1 times denoting frequency (whereas Email in table1 will only show up 1time), so somebody's email may show up 6 times meaning this person has showed up 6times.

There's another thing that I'd like to accomplish, from table2, I'd like to do a count of those whose email has show up for more than 10 times, between 5 to 10 times, 2 to 4 times and 1 time. Is this possible?

My apology that I may not have described my data table well, please ask me questions if you find my description/question unclear. Thank you so much for your help.

Sameul

Re: Calculate/Expression from a specific table loaded into Qlikview

Anonymous — Tue, 03 Dec 2013 21:09:50 GMT

Samuel,

See attached. Maybe it is not exactly what you need, but should be helpful.

Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Tue, 03 Dec 2013 21:14:45 GMT

Hi Michael,

thank you for your response. I am currently using evaluation edition/version of Qlikview, and I have reached the max limit of opening qvw files. Do you mind post the lines your use for the expression and maybe some screenshots?

Thank you so much!!

Re: Calculate/Expression from a specific table loaded into Qlikview

Anonymous — Tue, 03 Dec 2013 21:23:18 GMT

I can describe.

Table1 - only one field Email. Contains all values.
Table2 - field Email, contains subset of the values from Table1. Field "test" which is a copy of Email. Field ID.

Chart where Emails from Table2 are counted has expression
count({<ID={*}>} distinct Email)
I didn't use any dimensions here, but you can if needed.

Chart where Emails are counted per frequency has four expressions
>10: count({<ID={*}>} if(aggr(count(test),Email)>10,Email))
5 to 10: count({<ID={*}>} if(aggr(count(test),Email)>=5 and aggr(count(test),Email)<=10,Email))
2 to 4: count({<ID={*}>} if(aggr(count(test),Email)>=2 and aggr(count(test),Email)<=4,Email))
1: count({<ID={*}>} if(aggr(count(test),Email)=1,Email))
No dimensions

Regards,

Michael

Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Tue, 03 Dec 2013 21:26:25 GMT

ok, i will give it a try and get back to you soon, many thanks~

Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Thu, 05 Dec 2013 00:56:57 GMT

Michael,

Thank you for taking the time. For the first expression, count({<ID={*}>} distinct Email), it generates a bar chart of one bar giving the number of email in both table (or maybe just one), but note that Table1 and Table2 both share the same name for field Email.

By the way, the email list contain in Table1 may not be a whole list (meaning that the email contain in table2 may not be found in Table2) **~**

For frequency, I think your code is getting pretty close, right now it's it's showing only one bar (the same bar from **~** (i think you should be able to understand it once seeing the picture2 I attached)

Also, I am hoping that these charts can be linked to the rest of the database, meaning that if I click on the frequency more than 10, then my tables, charts and other list boxes will be updated simultaneously (right now it doesn't seem like it's doing that)

Gratefully thank you for the help!

Samuel

Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Thu, 05 Dec 2013 01:14:45 GMT

Michael,

I thought to add a free more things, hopefully to make it more clear.

EmailTest is generated after I load table 2 with the code below (where TableZYX is Table2)

LOAD Email as EmailTest

FROM

[Current Testing File\XYZ.xlsx]

(ooxml, embedded labels, table is ZYX);

So far, I can easily create a chart like this:

simply with the expression: count(EmailTest) with EmailTest as the dimension., but the problem is that, EmailTest isn't linked to the rest of the Database

Also, when I tried to create a similar chart with count(Email)-1 with Email as the dimension, looks like it only look at the Email in Table1 but NOT both Table1 and Table2 (oh and by the way, the Email in Table1 is also not unique, so it can show up for more than once)

Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Thu, 05 Dec 2013 03:39:55 GMT

Michael,

Actually, I should have mentioned that, for table2 there's two fields that can be used as an unique field, Last Name and First Name. I think I can load to combine them to be "full name". This won't always be a unique field though since some people may have the same first and last names.

However, even after combining these two fields, I still don't have a solution of how to achieve what i mentioned previously.

Thanks,

Samuel

Re: Calculate/Expression from a specific table loaded into Qlikview

Anonymous — Thu, 05 Dec 2013 16:05:09 GMT

Samuel,

First, it doesn't matter if Table1 has all Email values or not. As soon as ID field in the expression count({<ID={*}>} distinct Email) belongs to the Table2, the result will be only fort the Table2.

Second, the frequency chart which shows only one bar. Either there are no results or you missed something. Actually, I see at least one the problem - you created EmailTest as logical island, not connected to anything. Try this:

LOAD
Email,
Email as EmailTest
FROM [Current Testing File\XYZ.xlsx] (ooxml, embedded labels, table is ZYX);

If you need further help, upload your application, I'll take a look.

Regards,
Michael

Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Thu, 05 Dec 2013 19:43:49 GMT

Michael,

It worked. Could you please tell me what's the difference? Why it makes a difference loading Email one more time? I have some limited programming background, it has been a hard time to understand Qlikview's programming language.

This is exactly what I wanted, EXCEPT that it's NOT clickable. You know how bar chart's bars are clickable, but this one is not for some reason, But when I click on other bar chart (or list boxes) this chart does get reloaded so it's attached with the rest of the data.

Do you need my qlikview file to solve this problem? If I pass you my qvw file, do I need to give you my excel data file as well?

Many Thanks

Samuel

Re: Calculate/Expression from a specific table loaded into Qlikview

Anonymous — Thu, 05 Dec 2013 20:33:52 GMT

Samuel,

You created field TestEmail as data island - not connect to the rest of the data model. Hence, it didn't do it any good. Nothing is wrong in using data islands, they have there usage, but not here.
The field Email links it to the other tables. In my example, which you cannot open, I simply created additional field in the existing Table2 rather than in a separate table. Logically it doesn't matter in your case. But in general the fewer tables the better.

Next, nothing's changing on click because in this case each bar represents separate expression. Typically it is not the case - there is one expression, and bars represent values of the chart dimension. Clicking on bar selects value in the dimension. In the "dimensionless" chart, like here, there is nothing to select.
I can't tell if it is possible in your case. Upload your application (only qvw), and tell me what you want to get selected on click. I'll try when I have time.

Re: Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Fri, 06 Dec 2013 00:09:20 GMT

Michael,

Thank you for the explanation. It's superly appreciated~~

The attached is my Qlikview File, I have many tabs, the one that I am using to test is called "Testing."

(I created a new field called "Blank Dimension," and it's just "Blank Dimension" for all the cells in the column in Table2 hoping to use Blank Dimension as the dimension to make bars clickable, no luck)

Thank you so much for taking the time helping me to get a solution.

I am hoping for the good news soon~

Samuel

Re: Re: Calculate/Expression from a specific table loaded into Qlikview

Anonymous — Fri, 06 Dec 2013 14:12:17 GMT

Samuel,

First, you can't use a field in your expressions if the field doesn't exist. Expression
count({<ID={*}>} distinct Email)
was about my example which you couldn't open. I mentioned that there was field ID in Table2. You don't have it, so the expression is incorrect. In your case is better to use simple
count(distinct EmailTest)
Because the field EmailTest is what you want to count, and it exists only in Table2.

Second, it is possible to make the bars "clickable". Here is how:
Create calculated dimension

dual(
if(aggr(count([EmailTest]),Email)>10, '>10 Appt',
if(aggr(count([EmailTest]),Email)>=5, '5 to 10 Appt',
if(aggr(count([EmailTest]),Email)>=2, '2 to 4 Appt', '1 Appt')))
,
if(aggr(count([EmailTest]),Email)>10, 1,
if(aggr(count([EmailTest]),Email)>=5, 2,
if(aggr(count([EmailTest]),Email)>=2, 3, 4)))
)

In the sort tab, sort it by Numeric Value.
Remove all expressions and replace with one expression:
count(distinct EmailTest).

Now clicking on a bar selects Emails that are counted in this bar.

Additional recommendation - if you're going to keep dimension Program, check the box "suppress when value is null".

Hope I've answered all your questions this time...

Regards,
Michael
PS: Please don't upload large files. You can always reduce data and create a smaller version.

Re: Re: Calculate/Expression from a specific table loaded into Qlikview

samuel_lin — Fri, 06 Dec 2013 18:08:58 GMT

Michael,

sorry about the large file, I will try to attach a smaller size the next time.

I really do admire your understanding and skills over Qlikview coding, how should I train myself to be better? I should have mentioned that I am still a college student, I really enjoy working on Qlikview and this is my second time evaluating Qlikview for an organization.

I am feeling about right to close this close this case to become "answered." There's one more thing that I wanted to ask, how do I deselect Qlikview? I think I remember there's a way to do it. For what I wanted to accomplish in this thread, will I be able to have a bar for 0 time and do the count in the same way?

The fall semester is about to be over, these next two weeks are finals weeks. I am hoping to work on Qlikview some more in January, If I have development questions, may I forward my posts in Qlikview community to you?

Many thanks,

Samuel

Re: Re: Calculate/Expression from a specific table loaded into Qlikview

Anonymous — Fri, 06 Dec 2013 20:16:01 GMT

Samuel,

It possible to count Emails that don't have EmailTest associated with them. Try to change chart's calculated dimension to this

=dual(

if(aggr(count([EmailTest]),Email)>10, '>10 Appt',

if(aggr(count([EmailTest]),Email)>=5, '5 to 10 Appt',

if(aggr(count([EmailTest]),Email)>=2, '2 to 4 Appt',

if(aggr(count([EmailTest]),Email)=1, '1 Appt',

if(aggr(count([EmailTest]),Email)=0, '0 Appt')))))

if(aggr(count([EmailTest]),Email)>10, 1,

if(aggr(count([EmailTest]),Email)>=5, 2,

if(aggr(count([EmailTest]),Email)>=2, 3,

if(aggr(count([EmailTest]),Email)=1, 4, 5))))

)

And, change the expression to this:

count(distinct Email)

I'm not sure you'll like it because 0's bar is way larger than others. Anyway, for the sake of trying...

I'm sure you'll be back to QlikView, it is addictive . You'll be better with time. Go through the QV training materials, try exercises, maybe get a book. The best training - try to answer questions on QlikCommunity.

I do not recommend forwarding posts to me (or to anyone in particular) directly. There are hundreds of very good QV specialists here, many of them better than I, and they'll be happy to help. And I am not always available.

Good like with your finals.

Regards,

Michael