topic How to create a filter in QlikView

How to create a filter

Mon, 22 Aug 2011 06:51:22 GMT

Greetings,

I am using Qlikview 10 personal edition.

I would like to ask you how to create a filter in Qlikview.

Actually I have created tow straight boxes there I have included wo labels one is "Matching_status' which is showing whether the record is "matched" or "unmatched" and another one is "service_status" which is showing whether it is "included" or "excluded".

When I am creating a listbox and in general tab, in the field section when I am giving the expression as " if(x=y, 'Included', 'Excluded').. it is showing me two fields as Included, Excluded in the listbox.. But default is taking the first one and the second one is not functioning at all..

Hope you are all getting my point.

Please explain how to resolve it.

Re: How to create a filter

Mon, 22 Aug 2011 07:03:28 GMT

I mean to say it is only taking the True value not the false value. why don't understand.

Re: How to create a filter

Mon, 22 Aug 2011 07:09:49 GMT

It is not completely clear whay you mean, can you post your report as an example?

Regards,

Bert

Re: How to create a filter

Mon, 22 Aug 2011 07:52:55 GMT

The report scenario is : There are straight tables one is Account another is contact..

My task is comparing data from two datasources x and y.

in Account straightable I have x.x1,y.x1, x.x2,y.x2 and so on... Same here in Contact table.. I have added one label as "Matching Status"..

There I am comparing these data..

But I have to filter these data so that they can see only matching or unmatching data.. So I have created one listbox.. But it is showing only the true value means only the matching data not the unmatching data..

Hope this explanation will help you to understand my query.

Many Thanks

Sandeepa

Re: How to create a filter

Mon, 22 Aug 2011 08:04:59 GMT

Hi Sandeepa,

Can you post a snippet of your script where you determine the matching / unmatching part?

Regards,

Bert

Re: How to create a filter

Mon, 22 Aug 2011 09:03:51 GMT

For contacts:

if(med_title=aspen_title and med_persontypename=aspen_persontypename and med_initials=aspen_initials and med_firstname=aspen_firstname and med_lastname=aspen_lastname and med_recordstatus=aspen_recordstatus and aspen_gender=med_gender and aspen_person_service=med_person_service, 'matched','unmatched')

For Accounts:

if(med_orgname=aspen_orgname and med_orgstatusname=aspen_orgstatusname and med_orgtypename=aspen_orgtypename and postal_street=med_postalstreet and shipping_street=med_shippingstreet ,'matched','unmatched')

In listbox I am combining both.

Re: How to create a filter

Mon, 22 Aug 2011 09:21:35 GMT

if((med_orgname=aspen_orgname and med_orgstatusname=aspen_orgstatusname and med_orgtypename=aspen_orgtypename and postal_street=med_postalstreet

and shipping_street=med_shippingstreet) and (med_title=aspen_title and med_persontypename=aspen_persontypename and med_initials=aspen_initials

and med_firstname=aspen_firstname and med_lastname=aspen_lastname and med_recordstatus=aspen_recordstatus and aspen_gender=med_gender

and aspen_person_service=med_person_service),'matched','unmatched')

if(aspen_person_service=med_person_service, 'Included', 'Excluded')

This is the list box query

I don't understand why it is showing only the True values..

I am writing the expression in Generaltab->field->expression.

Re: How to create a filter

Mon, 22 Aug 2011 09:26:57 GMT

Hi Sandeepa,

The logic in the listbox should be moved to the script. The resulting column should be shown in the listbox.

Regards,

Bert

Re: How to create a filter

Mon, 22 Aug 2011 09:38:23 GMT

Please explain in brief..

Re: How to create a filter

Mon, 22 Aug 2011 09:56:18 GMT

Hi,

You need to make sure that the unmatched definition is linked to the unmatched data.

Something like this has to be created:

Tables created earlier in script:

Table A

TableA.ID

....

TableA.Matched

Table B

TableB.ID

TableA.ID

....

TableB.Matched

New script part:

MatchLink:

LOAD TableA.ID,

TableA.Matched

resident TableA;

left join (MatchLink)

LOAD TableA.ID,

TableB.ID,

TableB.Matched

resident TableB;

join (MatchLink)

TableA.ID,

TableB.ID,

if(TableA.Matched='Matched' and TableB.Matched='Matched', 'Matched', ' Not matched') as Matched

resident MatchLink;

drop field TableA.Matched, TableB.Matched from MatchLink;

regards,

Bert

Re: How to create a filter

Mon, 22 Aug 2011 11:55:37 GMT

Hello Bert,

Please find attached of the file.

By seeing this picture you will able to know that what exactly I want to explain you.

You can see there are two straightbox tables.

Account_Aspen vs Account_medpages in this table I am compiring data from two different data sources one is from aspen another is from medpages.

Like wise in Contact_aspen vs Contact_medpages.

Now I have to create flags which will filter only matched/unmatched/included/excluded.

I have gone through the script which you have posted. But do you think for creating flag(filter) I need to write those script.

If yes then how. Each table has data from two different datasources..

And now I am totally confused and lost.

Re: How to create a filter

Mon, 22 Aug 2011 13:24:24 GMT

See attached example report

Re: How to create a filter

Mon, 22 Aug 2011 14:11:31 GMT

Hi Bert,

Your example is Perfect.

You are using "dataA" and "matchedA".But thing is that the "matchedA" field is defined by me.. Acually it's a label and I have written the expression for it.

And it is not possible to define one by one which is matched or unmatched as there are billions of records.

That's the problem why I can not able to do it.

Any other solution is there.

Please have a look on that .bmp file which I have sent you.

There you can able to see two straighboxes are created i-account_aspen vs account_med ii- contact_aspen vs contact_med.

The first table contents are :

orgcode, aspen_orgname, med_orgname, aspen_orgstatusname, med_orgstatusname, aspen_orgtypename,med_orgtypename,postal_street, med_postalstreet, shipping_street, med_shippingstreet, Matching_status.

Matching_status field is added by me actually it's a label and the expression is "

if(med_orgname=aspen_orgname and med_orgstatusname=aspen_orgstatusname and med_orgtypename=aspen_orgtypename

and postal_street=med_postalstreet and shipping_street=med_shippingstreet,'matched','unmatched')".

The fields in 2nd table :

personcode, aspen_initial, med_initial, aspen_firstname, med_firstname, and so on....

In second table service_status is a label and the expression is

if(aspen_person_service=med_person_service, 'Included', 'Excluded')

Now I have to add flags which will filter only matched/unmatched/excluded/included.

Note: Aspen and medpages are two different datasources from which data are loading.

May be this explanation will make you clear what I am trying to convey .

Re: How to create a filter

Mon, 22 Aug 2011 14:57:39 GMT

Do you mean something like this

Re: How to create a filter

Mon, 22 Aug 2011 15:39:24 GMT

Finally I have succeeded.

Don't know how to thank you...

You have rescued me from a big trouble. Thanks a ton.

Re: How to create a filter

Tue, 23 Aug 2011 06:57:05 GMT

You can replace the inline loads with normal loads from database, excel files or whatever datasource you use.

Regards,

Bert

Re: How to create a filter

Tue, 23 Aug 2011 13:34:46 GMT

Hi Bert,

There is an issue I am experiencing; when I am joining it's giving me the duplicate "orgcode".

So I have used inner join.. Then also it is giving me the redundant values..

I think when I am joining it's join as crosstab join.

suppose orgcode, aspen_orgname, med_orgname

3, null, value

3,value,null

3,value,value

It's giving me this kinda output ...

How to get rid of it..

How to create a filter

Tue, 23 Aug 2011 13:49:35 GMT

Can you post the part of the script where you load the tables and perform the join?

Re: How to create a filter

Tue, 23 Aug 2011 13:53:04 GMT

Let me explain you what I have done.

in edit script in the first tab I have loaded data from txt file ie aspen_org, in the 2nd tab med_org, in the 3rd tab I am loading as Inline.

The field names are same in aspen_org and med_org.

How to create a filter

Tue, 23 Aug 2011 14:18:01 GMT

Hi Sandeepa,

Why are you doing an inline load?

A join between the two tables should be sufficient.

Something like:

join(TableA)

Load suppose orgcode,

aspen_orgname

resident TableB

It would ease things if you could post your complete script.

Regards,

Bert