Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
I would create a flag on the back-end as Y or N. Please check out the attached file.
Thanks
AJ
Ok, thank you,
And now, if i want to filter all records where Field1 is IN field2 ?
An idea?
thank you.
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.
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?
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
AJ
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.
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
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...
Can you also attach the xlsx file if possible.