Problem with comparison between variable & loaded field
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???????
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:
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.
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.
