Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
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