topic Re: Button and filter in two field in QlikView

Button and filter in two field

Wed, 05 Mar 2014 16:37:27 GMT

Hello,

I want to know if it's possible to filter in two field with a button like this :

Field1 = Field2

I want to filter records where field2 contain field1 values.

Exemple (after filter i want to show only the red values) :

Field1	Field2
Value1
Val	Value1
Val	ValueXX
Value2

Thank for your help

Re: Button and filter in two field

Wed, 05 Mar 2014 16:44:33 GMT

Hi,

I would create a flag on the back-end as Y or N. Please check out the attached file.

Thanks

Re: Button and filter in two field

Wed, 05 Mar 2014 17:38:16 GMT

Ok, thank you,

And now, if i want to filter all records where Field1 is IN field2 ?

An idea?

thank you.

Re: Button and filter in two field

Oleg_Troyansky — Wed, 05 Mar 2014 17:46:00 GMT

It's important to understand the terminology and the meaning of the word "filter". In QlikView, when we make a selection, we apply it to a specific Field. The Field Selection is in turn affecting the available data set, but it's important to emphasize that the selection is made on a specific Field. For example:

- Select all Customers that ... (the selection is made on the field Customer)

- Select all Items that ... (the selection is made on the field Item.

With that in mind, we can't request to " filter all records where Field1 is IN field2" - this is meaningless in QlikView. We can make a Selection in Field 1 and select those values that are also available in FIeld2. In Set Analysis terminology, it would look like this:

Field1 = P(Field2)

hope it helps to clarify the issue.

Re: Button and filter in two field

Wed, 05 Mar 2014 17:51:59 GMT

Ok but in SQL I Can do this :

Select field1 From Table where field1 IN (select field2 from table)

It is realy not possible?

Can I put my sql query in the script? how to?

Re: Re: Button and filter in two field

Wed, 05 Mar 2014 17:53:11 GMT

I created a field called InField which has 1. If you click it you will filter for all the data common in both fields.

If this doesn't meet your needs, can you please attached a sample doc. It will be greatly helpful in addressing this.

Thanks

Re: Button and filter in two field

Wed, 05 Mar 2014 17:57:31 GMT

So what you mentioned is a backend script. You can use the same script in QV and do a preload.

Load * ;

SQL Select *

From DB

where field1 IN (select field2 from table);

But this will filter the data thats brought into QV.

Re: Button and filter in two field

Oleg_Troyansky — Wed, 05 Mar 2014 18:22:10 GMT

OK, so you wanted to filter the data that you load in the script... Get it now.

Yes, you can do it in two ways.

1. The standard SQL syntax should work in all the SQL SELECT statements. So, as long as this is a SQL load from the database, your original SQL statement should hold true. I believe you need to use an embedded SELECT statement to implement what you are looking for, in SQL.

2. In the QlikView LOAD statement, that you use for any text file loads and resident loads, you cannot use the SQL clause IN - it's simply not supported by the syntax of the LOAD statement. Depending on your needs, you may be able to achieve similar results with one of the two functions:

EXISTS(ExistingField, NewField) - returns true when the evaluated value of the NewField exists in the field ExistingField

MATCH(TestValue, Value1, Value2, ...) - returns true when the TestValue matches one of the listed Values (this is the closest match to the original SQL function IN() )

good luck!

Oleg Troyansky

Come and learn advanced QlikView techniques with me at the Masters Summit in Chicago - April 1-3

www.masterssummit.com

Re: Button and filter in two field

Wed, 05 Mar 2014 18:52:57 GMT

Thank you,

I work with .xls files and i can't do something like this :

Table1:

LOAD ITEM,

OtherField,

Field1,

Field2

FROM

(ooxml, embedded labels, table is Sheet1);

Query:

Select * From Table1;

Could you help me?

It's hard to understand the logic of QLikView...

Re: Button and filter in two field

Wed, 05 Mar 2014 18:57:54 GMT

Can you also attach the xlsx file if possible.

Re: Button and filter in two field

Oleg_Troyansky — Wed, 05 Mar 2014 19:24:30 GMT

Well, this is not so much about QlikView, as it is about merging data from different sources.

Your SQL SELECT statement will get performed on your database, QlikView is just passing the syntax back to the database. Obviously, your database knows nothing about your Excel spreadsheet.

However, using QlikView script capabilities, you can create a string that contains a list of relevant values that you can then use in your SQL SELECT statement. So for example, if you wanted something like this:

SQL SELECT Field1, Field2 FROM ...

Where Field1 IN ('A', 'B', 'C')

you could create a variable that holds the list of the values, with the single quotes in it, and use the variable in your SQL SELECT:

LET myVar = CONCAT( ... );

SQL SELECT Field1, Field2 FROM ...

Where Field1 IN ($(myVar))

It takes quite a bit of work to build the actual solution for you, but then it would be called consulting, wouldn't it? However, this is a direction that should take you to the solution.

cheers,

Oleg Troyansky

Re: Button and filter in two field

Wed, 05 Mar 2014 19:25:49 GMT

For your information, values are not real, i made a file exemple.

https://drive.google.com/file/d/0B_KydpDcKkXBeTNPdDRlcWRJX1U/edit?usp=sharing

Re: Button and filter in two field

Wed, 05 Mar 2014 19:39:25 GMT

Ok, i understand...

So, QlikView is a good tool to work with different database (sql, xls, csv....) but it's not the best tool for modulating data?

I'm right?

Re: Button and filter in two field

Oleg_Troyansky — Wed, 05 Mar 2014 19:50:01 GMT

It's a matter of personal preference, but personally I'd disagree. In my practice, QlikView is an excellent tool for anything related to ETL, including data modeling and dimensional modeling. As with any tool, one needs to know what one is doing, especially for any complex issues.

Re: Button and filter in two field

Wed, 05 Mar 2014 20:14:21 GMT

I understand your feeling, but i found that QlikView is not as logical as other languages like C+, VB, VBA, PHP...

QlikView is using specific code, specific objects, many specific things.

I'm not saying that QlikView is too bad, i just want to say that this tool is hard to understanding when you are accustomed to develop with others languages.

Re: Button and filter in two field

vikasmahajan — Thu, 06 Mar 2014 08:56:38 GMT

PFA Sample Application for the same.

Vikas

Re: Button and filter in two field

ashwanin — Thu, 06 Mar 2014 10:11:45 GMT

You can create Flag for the records which are equal and which are not equal. with Yes or No

Select Yes to check the record which are matching and no for the records which are not matching .

Re: Button and filter in two field

sudeepkm — Thu, 06 Mar 2014 10:21:22 GMT

I would suggest to compare QlikView with other BI tools instead of comparing it to a language.

I think the most important thing is to identify the requirement properly then select whether a BI Tool or ETL tool or an Application developed using language can provide the solution.

It is also important to look at things from a high level covering all the factor associated to it.

Re: Re: Button and filter in two field

Thu, 06 Mar 2014 15:35:45 GMT

Attached with answer. I created a field called CommonField. If you select 1 in it, it will give you the results you are looking for.

Please let me know if you have any other questions.

Thanks