Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

Nested If

I have the following QlikView model:

GenericDrugEntryDateTwo.JPG

I need to add a calculated dimension called "Generic Drug Entry Date".  This is calculated on the basis of the following fields:

  1. DrugSubstanceFlag
  2. Patent Expiry Date
  3. ExclusivityDate

DrugSubstanceFlag takes two values either 'Y' or 'N'.

If the DrugSubstanceFlag = 'Y' and "Patent Expiry Date" is greater than "ExclusivityDate", then "Generic Drug Entry Date" =  "Patent Expiry Date", Otherwise "Generic Drug Entry Date" = "Exclusivity Date".  However an item/product denoted by "Trade Name" can have more than two "DrugSubstanceFlag" = 'Y', in this case the higher "Patent Expiry Date" will be the "Generic Drug Entry Date"

If the DrugSubstanceFlag = 'N' , then the "Generic Drug Entry Date" is Today's date.

The resulting table should look as follows:

GenericDrugEntryDateThree.JPG

There should only be a single Generic Drug Entry Date for each Trade Name entry

How can i accomplish the above?

Thank you in advance.

6 Replies
vishsaggi
Champion III
Champion III

Did not understand what you mean by this?

"However an item/product denoted by "Trade Name" can have more than two "DrugSubstanceFlag" = 'Y', in this case the higher "Patent Expiry Date" will be the "Generic Drug Entry Date"


can you elaborate please.

However, for the other conditions you mentioned you can try like below:

     IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] > [ExclusivityDate], [Patent Expiry Date],

        IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] < [ExclusivityDate], [Exclusivity Date],

           IF(DrugSubstanceFlag = 'N', Today(), [Generic Drug Entry Date]))) AS GenericDrugEntryDate

chitemerere
Specialist
Specialist
Author

Thank you very much.  I am using a calculated dimension which is as follows:

=IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] > [ExclusivityDate] or If(DrugSubstanceFlag = ' ' and [Patent Expiry Date] > ExclusivityDate, [Patent Expiry Date]), If(DrugSubstanceFlag = ' ', Now(),ExclusivityDate))

I get the following table, which is not what i am looking for, dates in the Generic Entry Date column are not dates:


GenericDrugEntryDateTable.JPG

Regards


Chris

vishsaggi
Champion III
Champion III

So just use the Date() function around the expr like

Date(yourexpr, 'DD/MM/YYYY')

chitemerere
Specialist
Specialist
Author

Thanks for your input but does not work in my case

chitemerere
Specialist
Specialist
Author

I have used the following Calculated Dimension:

=IF(DrugSubstanceFlag = 'Y' and [Patent Expiry Date] > [ExclusivityDate] or If(DrugSubstanceFlag = ' ' and [Patent Expiry Date] > ExclusivityDate, [Patent Expiry Date]),ExclusivityDate)

With the following results


GenericDrugEntryDateTable3.JPG

The results are almost there but wrong in the case of the circled data.  Expiration date is greater than ExclusivityDate and should therefore be the Generic Entry Date


Thanks

vishsaggi
Champion III
Champion III

‌will check tomorrow. Don’t have access to the system.