Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
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
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
];
Thanks Maxgro. This makes sense