topic Re: Isnull Field Selection in QlikView

Isnull Field Selection

Anonymous — Sat, 09 Sep 2017 20:55:17 GMT

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

Re: Isnull Field Selection

Anil_Babu_Samineni — Sun, 10 Sep 2017 01:23:00 GMT

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

Re: Isnull Field Selection

vinieme12 — Sun, 10 Sep 2017 07:39:13 GMT

Post a sample app

How to get answers to your post?

Qlik Community Tip: Posting Successful Discussion Threads

Re: Isnull Field Selection

qlikviewwizard — Sun, 10 Sep 2017 07:48:39 GMT

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

Re: Isnull Field Selection

Anonymous — Sun, 10 Sep 2017 23:46:09 GMT

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

Re: Isnull Field Selection

Anonymous — Sun, 10 Sep 2017 23:49:20 GMT

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

Anil_Babu_Samineni — Mon, 11 Sep 2017 03:27:55 GMT

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

Re: Isnull Field Selection

Anonymous — Mon, 11 Sep 2017 22:06:59 GMT

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

Anil_Babu_Samineni — Tue, 12 Sep 2017 02:07:23 GMT

+ marcus_sommer‌

Re: Isnull Field Selection

marcus_sommer — Tue, 12 Sep 2017 06:52:08 GMT

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