Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gregoryt40
New Contributor

Comparing Two Fields - Only records that exist in both sets

Hello,


I have a table with key fields that are as follows:

Primary SecIDiRisk Average LifeYear
1

4

12/31/14
2412/31/14
1512/31/15
316/30/15

I have used a calendar object to create two date variables (vStartDate and vEndDate).

I want to compare a record (e.g. [iRisk Average Life]) across the two dates selected but only display the comparison if there is a match in the unique identifying field, which is [Primary SecID] and there is a difference between the two field in the two dates.

That is I want to only show those records where the 'Primary SecID' has changed from Date 1 to Date 2.

In this instance, if I selected 12/31/14 as date 1 and 12/31/15 as date 2, the results would be as folllows:

Primary SecIDiRisk Average Life - 12/31/14iRisk Average Life - 12/31/15DifferenceYear
145-1

I am thinking the formula should be something like this...


sum({$<A_ASOFDATE={vStartDate},[iRisk Average Life] = p({$<A_ASOFDATE={vEndDate}>}[Primary SecID)>}IDATE)


Please help, thank you!

5 Replies
MVP
MVP

Re: Comparing Two Fields - Only records that exist in both sets

Maybe like attached (two alternatives shown), e.g. second alternative shows straight table with dimension

Primary SecID and three expressions:

=if(Column(3),Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]))

=if(Column(3),Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life]))

=Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]) - Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life])

gregoryt40
New Contributor

Re: Comparing Two Fields - Only records that exist in both sets

Thank you!  In this example.  What is "Column(3)" referencing to?

I am trying to build this in my dashboard but it is not working.

MVP
MVP

Re: Comparing Two Fields - Only records that exist in both sets

Column(3) is referencing to the third expression column

=Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]) - Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life])

If all three expressions return zero or NULL and if 'suppress zero values' is enabled on presentation tab, only Primary SecID with a difference between years in field iRisk Average Life will be shown.

gregoryt40
New Contributor

Re: Comparing Two Fields - Only records that exist in both sets

Hey!


Thank you so  much!  It is working, however it is still showing results where the value exists for one date and not the other even though "Supress null values" is selected.

Any ideas?

MVP
MVP

Re: Comparing Two Fields - Only records that exist in both sets

Maybe add a condition:

If( Count({<Year = {'$(vStartDate)','$(vEndDate)'} >} DISTINCT [iRisk Average Life]) =2,

Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]) - Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life]),

NULL()

)

Community Browser