Isnull Field Selection

[Anonymous]

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:

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

[Anil_Babu_Samineni]

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

[vinieme12]

Post a sample app

How to get answers to your post?

Qlik Community Tip: Posting Successful Discussion Threads

[qlikviewwizard]

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

[Anonymous]

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

[Anonymous]

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.

[Anil_Babu_Samineni]

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

[Anonymous]

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

[Anil_Babu_Samineni]

+ marcus_sommer‌

[marcus_sommer]

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