topic Re: How to LOAD attribute boolean values in QlikView

How to LOAD attribute boolean values

Wed, 03 Jun 2015 08:55:58 GMT

Hi,

I am trying to create a table in QlikView, using attributes from an SQL database.

The table in the database is composed of "Boolean" values, which are shown as either "0" or "1".

I want to load, for example, only those data which have a value of TRUE (meaning : "1")

Here is my load script :

Customers :

LOAD

personHasEmail,

(personHasEmail = '1') as Email_YES

(Meaning : ALL data, where the person has an email, will be taken from the database)

And, in QlikView, I have the following expressions :

Count (CustomerID)

Count(Email_YES)

(Each customer has a unique ID. So, "Count CustomerID" will produce the total number of people in the database)

And, "Count(Email_YES") should display ONLY those people who have email addresses.

But, this is not working;

Both expressions are displaying the TOTAL number of customers;

Obviously, the Count(Email_YES) expression is not working. Instead of counting only those people with emails, it's counting everyone.

Any help would be greatly appreciated.

Re: How to LOAD attribute boolean values

alexandros17 — Wed, 03 Jun 2015 09:00:08 GMT

Try this:

Customers :

LOAD

personHasEmail,

If(personHasEmail = '1', 1, 0) as Email_YES

then use

Count({$<Email_YES={'1'}>} CustomerID)

let me know

Re: How to LOAD attribute boolean values

Wed, 03 Jun 2015 09:07:19 GMT

Thanks, Allesandro.

Unfortunately, the expression produced a zero ("0") value.

Re: How to LOAD attribute boolean values

awhitfield — Wed, 03 Jun 2015 09:11:08 GMT

Hi Stepen,

see the attached example:

Script

Data:
Load * Inline
[
Customer, EmailFlag
A,1
B,1
C,1
D,0
];

Expression in chart

Count(If(EmailFlag = 1, Customer))

HTH Andy

Re: How to LOAD attribute boolean values

alexandros17 — Wed, 03 Jun 2015 09:14:08 GMT

Could you share your doc (or a similar example) ?

Re: How to LOAD attribute boolean values

Wed, 03 Jun 2015 09:22:38 GMT

Hi Andy,

thanks for your reply.

I assume, from your example, I should replace Customer with CustomerID ?

And, EmailFlag..........with "PersonHasEmail"?

I did that. And, I'm getting the same result : zero (0)

Re: How to LOAD attribute boolean values

Wed, 03 Jun 2015 09:23:06 GMT

Sorry, which doc?

I'm new to this

You mean the results I am getting?

Re: How to LOAD attribute boolean values

alexandros17 — Wed, 03 Jun 2015 09:24:41 GMT

The qlikview file (.qvw)

Re: How to LOAD attribute boolean values

awhitfield — Wed, 03 Jun 2015 09:28:04 GMT

HI Stephen,

the example was just to show the principle, upload your QVW please.

Andy

Re: How to LOAD attribute boolean values

Gabriel — Wed, 03 Jun 2015 09:40:38 GMT

Hi,

I think you should just load everything as it is, and in your expression in UI, you can count where Email_Yes= 1.

As in

LOAD

personHasEmail,

personHasEmail as Email_YES

From Source;

Then in your expression to count Email_Yes

Count({<Email_Yes = {1}>} Email_Yes)

This is work and you can Count your customer ID as well.

Re: How to LOAD attribute boolean values

Wed, 03 Jun 2015 10:01:42 GMT

Thank you Gabriel.

Actually, I realized my mistake : I should have left my original script the way it was..............just as Gabriel mentioned.

But, instead of trying to create a multi-table (which is what I was doing), I should have simply created two separate tables : one for Customers, and one for Emails.............and then cross-reference both counts into a THIRD table

This solved my problem

Thanks all for your helpful responses