Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
emyemyemy
Contributor III
Contributor III

when i reload my code : My let List is being invisible in my sql query code when it has no value

I have a let list  selected from an excel file  used in a where sql query  :

SQL
set @MyValue= (select Mycolumn from (select column as MyColumn from `MydataBase`.`MyTable`
where column  in ($(MyList)) ORDER BY ID desc limit  1 )a);

This code is correct  only  when i have a not null list   but  when i have an empty list   the reloaded code showed a synthax  error near  from 'where column in () order  by ID desc limit 1 )a);'

it shows like i  wrote nothing between  ' () ' !!

How can  I  make Mylist  visible in my code  even when it  is null   ? how can i add this ondition ?

Thank you for any help or attention  !

2 Solutions

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

I think you will need something like this:

if (len(trim($(MyList))) >= 1 then
   let vWhere = 'where column in ($(MyList))';
else
   let vWhere = '';
end if

SQL
set @MyValue= (select Mycolumn from (select column as MyColumn from `MydataBase`.`MyTable`
$(vWhere) ORDER BY ID desc limit  1 )a);

- Marcus

 

 

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

There was a bracket too much and the variable needs here to be set in quotes because the content is a string, therefore try it in this way:

if len(trim('$(MyList)')) >= 1 then
...

- Marcus

View solution in original post

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

I think you will need something like this:

if (len(trim($(MyList))) >= 1 then
   let vWhere = 'where column in ($(MyList))';
else
   let vWhere = '';
end if

SQL
set @MyValue= (select Mycolumn from (select column as MyColumn from `MydataBase`.`MyTable`
$(vWhere) ORDER BY ID desc limit  1 )a);

- Marcus

 

 

emyemyemy
Contributor III
Contributor III
Author

Hello Marcus !

Thank you for your answer , I did try your suggestion i think its  a great idea using the $(vWhere) either i got an error script at  'len(trim($(MyList))) '

I Think  this expression is incorrect counting  List items !

I am trying to figure out the problem , and grateful for any help or suggestion ! 

marcus_sommer
MVP & Luminary
MVP & Luminary

There was a bracket too much and the variable needs here to be set in quotes because the content is a string, therefore try it in this way:

if len(trim('$(MyList)')) >= 1 then
...

- Marcus