Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

macaulay
Contributor

Adding filter, that doesn't exist in source

Dear all,

I got an excel sheet with project numbers, that are marked as "open". In general I got over 8000 projects, but just around 800 of them are open projects, so the list just shows me these projects. In my final Qlik App I got the whole list with open and closed projects, but I want to filter between open and closed projects. As, in the mentioned list, I only got the open projects (and it would be kind of difficult to get the closed projects in the same way), I just get the filter "open".

So my question is, whether in Qlik a further filter can be defined by which all projects are shown, that aren't "open"?

Thanks for the answers and best regards

Tom

18 Replies

Re: Adding filter, that doesn't exist in source

When you load the data you can replace the null values with a real value like 'closed' so you will get two values you can filter on:

LOAD

     ....some fields....,

     If(Len(Trim(Status))>0,Status, 'closed') as Status,

     ....other fields....

FROM

     ....source_table....


talk is cheap, supply exceeds demand
macaulay
Contributor

Re: Adding filter, that doesn't exist in source

Thanks for your reply, badly I get the following error. Wondering what it means as Status is defined.

Error.JPG

prashantsanchet
Contributor

Re: Adding filter, that doesn't exist in source

You are creating two field /columns with same name like Status.


Please rename one of them and reload again.

Hope it will help.

macaulay
Contributor

Re: Adding filter, that doesn't exist in source

But if I rename one of the Status field it tells me, that there's no field f.e. "Status2" existing. So I don't really get it, sorry.

shraddha_g
Honored Contributor III

Re: Adding filter, that doesn't exist in source

Try Below,

LOAD

     ....some fields....,

     If(Len(Trim(Status))>0,Status, 'closed') as Status2,

     ....other fields....

FROM

     ....source_table....

shraddha_g
Honored Contributor III

Re: Adding filter, that doesn't exist in source

As per your screenshot,

Modify it as below:

Load

     Projekt,

     Status,

     If(Len(Trim(Status))>0,Status,'abgeschlossen') as Status2;

macaulay
Contributor

Re: Adding filter, that doesn't exist in source

Thanks four your reply, now the script loads and I got the wanted filters. But as I filter by the closed projects, it shows me no single project instead of the 7000 open ones. It seems as if every project is excluded by this way.

macaulay
Contributor

Re: Adding filter, that doesn't exist in source

I think the problem might be, that there's no direct correlation between the table I load which has the status of the project as content and the other tables, that have all the projects as content. Of course they are (in Qlik) connected by the projectnumber (which is the overall combining element), but maybe Qlik doesn't get the wanted conclusion, because the loaded table simply is empty when the open projects are dismissed.

shraddha_g
Honored Contributor III

Re: Adding filter, that doesn't exist in source

If possible share sample app with sample data