topic Re: Nested If in QlikView

Nested If

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following QlikView model:

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

DrugSubstanceFlag
Patent Expiry Date
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:

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.

Re: Nested If

vishsaggi — Tue, 11 Sep 2018 18:26:38 GMT

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

Re: Nested If

Anonymous — Tue, 11 Sep 2018 19:56:32 GMT

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:

Regards

Chris

Re: Nested If

vishsaggi — Tue, 11 Sep 2018 20:04:26 GMT

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

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

Re: Nested If

Anonymous — Tue, 11 Sep 2018 21:33:24 GMT

Thanks for your input but does not work in my case

Re: Nested If

Anonymous — Tue, 11 Sep 2018 21:38:12 GMT

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

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

Re: Nested If

vishsaggi — Wed, 12 Sep 2018 03:11:33 GMT

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