How to filter a column in a QVD file?

[Anonymous]

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

[Anonymous]

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

[maxgro · Accepted solution]

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

];

[Anonymous]

Thanks Maxgro. This makes sense