topic Problem with comparison between variable & loaded field in QlikView

Problem with comparison between variable & loaded field

Wed, 11 Feb 2015 14:57:52 GMT

I seem to be having a problem with my if condition. It works just fine on a date field but I seem to have a problem if the field is text.

I have two different files that I'm loading in;

Here is part of one of the files:

Load

CompanyID ,

670 as Parent_Comp,

AssetID,

AcquisitionDate,

Year(AcquisitionDate) as Acquisition_Year,

Month(AcquisitionDate) as Acquisition_Period,

WarrantyDate,

AcqValue

Here is part of the other file:

ASSETID,

SEQUENCE_NUMBER,

FISCAL_YEAR,

PARENT_COMP,

BEG_BALANCE,

BEG_DEPRECIATION

I have two created a pivot table to display my information and I also created a variable called vAssetYr so that the user can input what year they want to look at.

I have two different if conditions where one seems to work and the other doesn't and I suspect that it has something to do with the data type.

Here are the if conditions:

=if(Acquisition_Year=$(vAssetYr), AcqValue],0) (this one works)

=if(Fiscal_Year=$(vAssetYr), Beg_Depreciation]),0) (this one doesn't work)

I'm assuming this has something to do with the fact that Acquisition_Year is derived from a date field and

Fiscal_Year in the other file is coming in as a text field???????

How can I get around this?????

Re: Problem with comparison between variable & loaded field

marcus_sommer — Wed, 11 Feb 2015 16:03:22 GMT

It looks that these tables have no connection and you tried to select and calculate over variables - this isn't a recommended way to use qlikview. Therefore try to build a datamodel where the tables are assigned to each other and use for date-fields the approach with one or several master-calendar:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles#comment-12934

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year#comment-18790

- Marcus

Re: Problem with comparison between variable & loaded field

Wed, 11 Feb 2015 16:35:21 GMT

The tables are connected. I have a join on the tables & I just didn't put it in the writeup.

Also, I have 2 separate pivot tables for my data.

One table has this in the expression and it works just fine: =if(Acquisition_Year=$(vAssetYr), AcqValue,0)

The other table has this: =if(Fiscal_Year=$(vAssetYr), BegDepreciation],0) I can't seem to get any numbers to show up. It's almost like the fiscal year isn't being recognized. I created a mini table just to verify the fiscal year field has data and it definitely does. I have a record that contains 2014 for the fiscal_year and I'm inputting 2014 but it will not pull the record in my pivot table.

Re: Problem with comparison between variable & loaded field

marcus_sommer — Wed, 11 Feb 2015 16:46:37 GMT

Not each association will lead to the expected connection. Why didn't you use simple a field year to select the associated values in both tables?

Another possible reason to get not the expected values is to use fields without aggregation - if is only one fieldvalue available then an expression would return a result but if there are more the expression wille be return NULL.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/16/use-the-aggregation-functions

- Marcus