Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
cworkman2015
New Contributor III

Isnull Field Selection

Hello,

I am trying to capture all years in my data model in one field selection box. I'm doing this by using the isnull function Field Expression.

field expression: If(IsNull(Year),AvailableYear,Year)

However, when I make the selection on a known existing year from my calendar table (2017) - it captures that same year in the other table.

Here are the examples:

IsnullYearSelections.PNG

I would like to select 2017 or 2016 and it only capture the Calendar Year selection 2016 or 2017. Then, when I select 2018 it would have only AvailableYear 2018.

Thanks,

Channing

9 Replies

Re: Isnull Field Selection

If those Year and AvailableYear are mapping each together then you could need Alternate states to get into Current selections from 2 filters?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vinieme12
Esteemed Contributor II

Re: Isnull Field Selection

Arjunarao
Honored Contributor II

Re: Isnull Field Selection

Use if(len(trim (Year))>0, Available Year,OtherYear)

cworkman2015
New Contributor III

Re: Isnull Field Selection

Anil,

Thank you for your response. The relationship between those do exist through a certain key.

However, that relationship only exists up to the Period in the main fact table. Any Period greater than the main fact table, which would be any period greater than September 2017, ends that relationship - since there is no matching key.

I may need to set up a new question as I think this pertains to data modelling.

So, in my fact table I have resource, project, month, and year records.

I could join the availability table to the main fact table (resource, month, and year). Once I do that I could say if a certain period has a resource, project, month, and year - divide the total availability number by the number of records in the main table.

So, if I had 5 records which indicated Channing on 5 projects in September 2017 I would have 160 / 5. Then any period greater than that would be the total availability: 160.

Now, that's something I could do in SQL through a partition function but I'm not sure how to do it in Qlik.

I hope that explanation wasn't too incoherent.

What are your thoughts?

Thanks,

Channing

cworkman2015
New Contributor III

Re: Isnull Field Selection

If I do what I mentioned above I could just sum(Total<Month,Year> availability) and get the appropriate availability for Channing's month and year, regardless of duplication created through the join.

Re: Isnull Field Selection

I am not sure, How this expression helps you. Some where. Let's think this way because you need only selections with different respect

Load esource, month, year, year as annualyear from Fact;


Now, Create 2 boxes which has year and annualyear then select anything using alternate state Alternate States ‒ QlikView

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
cworkman2015
New Contributor III

Re: Isnull Field Selection

Anil,

I went ahead and partitioned as I mentioned above and fixed all this in the data model (I still have some issues but it's not appropriate for this thread so I'm going to open a new one).

I wanted to avoid the confusion of Alternate states - I feel that my users will not be able to adjust accordingly.

Thank you for your help!

Channing

Re: Isnull Field Selection

+ marcus_sommer

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP & Luminary
MVP & Luminary

Re: Isnull Field Selection

For me it's not quite clear in which relation those fields/tables are and therefore it's difficult to suggest anything. But if anyhow possible the problem should be addressed within the script and not in the gui. I think the merging of the tables goes in the right direction but it mustn't be mandatory made by joins. Joins could be depending on the relation between the tables quite difficult so that maybe a mapping is easier and more suitable.

I would go even a bit further to think that a concatenation (union in sql) is even more suitable by merging both year-fields into a single one and to add a flag to be able to differ between them and to come in the end to expressions like:

count({< YearFlag = {1} >} Project) / count({< YearFlag = {2} >} Project)

I'm not sure if it's applicable to your datamodel but I would start on this point.

- Marcus