Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

If statement

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!!!!




5 Replies
MVP & Luminary
MVP & Luminary

Re: If statement

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


talk is cheap, supply exceeds demand
MVP
MVP

Re: If statement

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)))

;

Not applicable

Re: If statement

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.


Not applicable

Re: If statement

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.

Not applicable

Re: If statement

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.

Community Browser