topic Re: If statement in QlikView

If statement

Fri, 25 Sep 2015 23:23:09 GMT

Hi All,

I have a requirement to build logic. The conditions are;

My conditions are as follows:

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND Code should be 10 OR Costdate (CD) should have been within 24 months from the date (SD)

Below is the code, please let me know if it is right:

IF(Floor(SD)<='12/31/2010'
AND Floor(AD)> Floor(Year(SD))+ 1
AND Floor(AD)>='01/01/2015' AND Floor(AD)<='12/31/2015'
AND (Code) ='10')

OR Floor(AD)> SD+24, '1', '0') as ABC ( Not sure how to code the last part Acquisitiondate (AD) should have been within 24 months from the Sales date (SD))

[Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND Code should be 10] This set fetches 3 records

[Costdate (CD) should have been within 24 months from the date (SD)] This filters to 2 records

I am trying to understand how does the OR operator behave in this case. I have attached sample data. from that should the final number of records should be????

HELP is greatly Appreciated!!!!

Re: If statement

Gysbert_Wassenaar — Sat, 26 Sep 2015 15:57:27 GMT

Sales Date (SD) should be before 2010: Floor(SD)<makedate(2010)

AND Acquisitiondate (AD) should be 1 year after the respective Sales date: AND AD=AddYears(SD,1)

AND Acquisitiondate (AD) should be within 2010: AND Year(AD) = 2010

AND Code should be 10: AND Code = 10

OR Costdate (CD) should have been within 24 months from the date (SD): OR Fabs(CD-SD)<365*2

Re: If statement

maxgro — Sat, 26 Sep 2015 18:45:21 GMT

LOAD SD, AD,

CD, Code

FROM

[New Test.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where

Year(SD) < 2010

and AD=AddYears(SD, 1)

and Year(AD)=2010

and (Code=10 or (CD<=AddMonths(SD,24) and CD>=AddMonths(SD,-24)))

;

Re: If statement

Mon, 28 Sep 2015 16:16:47 GMT

Thanks for your replies. I am trying to create fields for Denominator and Numerator.

My Denominator has the above conditions;

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND there should be at least 2 AD for a PKID

My Numerator has additional conditions;

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND there should be at least 2 AD for a PKID

AND Code should be 10 OR 11 OR 12 OR Costdate (CD) should have been within 24 months from the date (SD)

AND ICode should be 333 OR 444 OR Costdate (CD) should have been within 24 months from the date (SD)

AND Code should be 14 OR 15

Technically my numerator should fetch less number of values than my denominator as it is same conditions plus some more. However it is pulling more row. Excel data attached.

Re: If statement

Mon, 28 Sep 2015 20:24:14 GMT

Can somebody please help?

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND there should be at least 2 AD for a PKID

My Numerator has additional conditions;

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND there should be at least 2 AD for a PKID

AND Code should be 10 OR 11 OR 12 OR Costdate (CD) should have been within 24 months from the date (SD)

AND ICode should be 333 OR 444 OR Costdate (CD) should have been within 24 months from the date (SD)

AND Code should be 14 OR 15

Technically my numerator should fetch less number of values than my denominator as it is same conditions plus some more. However it is pulling more row. Excel data attached.

Re: If statement

Tue, 29 Sep 2015 16:56:04 GMT

Hi Experts,

I need some more help with the logic. Looking forward to your inputs!

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND there should be at least 2 AD for a PKID

My Numerator has additional conditions;

Sales Date (SD) should be before 2010

AND Acquisitiondate(AD) should be 1 year after the respective Sales date

AND Acquisitiondate (AD) should be within 2010

AND there should be at least 2 AD for a PKID

AND Code should be 10 OR 11 OR 12 OR Costdate (CD) should have been within 24 months from the date (SD)

AND ICode should be 333 OR 444 OR Costdate (CD) should have been within 24 months from the date (SD)

AND Code should be 14 OR 15

Technically my numerator should fetch less number of values than my denominator as it is same conditions plus some more. However it is pulling more rows. Excel data attached.