Adding filter, that doesn't exist in source

[Anonymous]

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

[Gysbert_Wassenaar]

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....

[Anonymous]

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

[prashantsanchet]

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

Please rename one of them and reload again.

Hope it will help.

[Anonymous]

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]

Try Below,

LOAD

     ....some fields....,

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

     ....other fields....

FROM

     ....source_table....

[shraddha_g]

As per your screenshot,

Modify it as below:

Load

     Projekt,

     Status,

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

[Anonymous]

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.

[Anonymous]

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]

If possible share sample app with sample data