Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
karthick01
Contributor III
Contributor III

How to filter a column in a QVD file?

Hi,

I understand that if we use a regular where clause in the QVD then the QVD becomes a non optimised QVD. The only exception is using 'Exists'.

I am trying to load a table from a QVD file and then i need to apply the logic where Name='XYZ'. How to do this using exists?

I tried the following script using "exists" but its not working.

Logic 1:

Directory;

LOAD Id,

     Name,

     Sales

FROM

[..\Test data\Join2.xlsx]

(ooxml, embedded labels, table is Sheet1) where exists('Name','XYZ');

But in the reference manual, I can see an example under the exists function:

exists( Month, 'Jan' )

returns -1 (true) if the field value Jan can be found in the

current contents of the field Month.

FYI,

Logic 2:

I tried a second logic that works perfectly and here is the logic:

1. Load the where clause values using an inline statement under a column name 'Name'

2. Then loaded the table using exists ('Name).

This logic works.

But I need to understand what is the issue with the Logic 1.

Thanks

1 Solution

Accepted Solutions
maxgro
MVP
MVP

you can do a small test: remove the where and add the exists as a field, result in image

Sales:

Load

  RowNo() As Row,

  User,

  Sales,

  If(Exists(User,User), 1, 0) As Exists,                    // same as Exists(User)

  If(Exists(User,'John'), 1, 0) As Exists2

;

Load * Inline [

User, Sales

John, 100

Tom, 200

Jane, 300

Robert, 400

Graham, 500

John, 600

];

1.png

View solution in original post

4 Replies
stabdha91
Contributor III
Contributor III

Hi Karthick,

You can use match function in such cases.

LOAD Id,

     Name,

     Sales

FROM

[..\Test data\Join2.xlsx]

(ooxml, embedded labels, table is Sheet1)

match(Name,'XYZ');

Regards,

Stabdha

kaushiknsolanki

Hi,

The issue with 1st Logic is, Exists() function takes the Field name to compare it values.

Just like in second case where you compared the one field which is from Inline with the existing field in Join2 Table.

Exists() function do not take value as parameter, and thus the 1st logic failed.

Hope you understood.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
maxgro
MVP
MVP

you can do a small test: remove the where and add the exists as a field, result in image

Sales:

Load

  RowNo() As Row,

  User,

  Sales,

  If(Exists(User,User), 1, 0) As Exists,                    // same as Exists(User)

  If(Exists(User,'John'), 1, 0) As Exists2

;

Load * Inline [

User, Sales

John, 100

Tom, 200

Jane, 300

Robert, 400

Graham, 500

John, 600

];

1.png

karthick01
Contributor III
Contributor III
Author

Thanks Maxgro. This makes sense