topic Re: Return a value for a Date Range in QlikView

Return a value for a Date Range

Fri, 12 Dec 2014 16:24:24 GMT

I have an instance where I have promotions that I want to identify in a seperate column anything that has a Promotion Start Date of TODAY out to 21 days, here is the script I have right now, I tried multiple OR statements for each today()+1 or today()+2, all the way out to +21 and it doesn't seem to work I also need to include another condition with an AND statement for the Column of PAYMENT TYPE must be equal to 'Off Invoice". Can anyone advise, the following is the script piece where I am using the promotion date:

LOAD

Promotion_Start_Date,
Date# (Promotion_Start_Date, 'MM/DD/YYYY') as DAY_COD,
if([Promotion_Start_Date] <= today()+21 and [Payment_Type] = 'Off Invoice','PENDING') as OI_Active_Within_3_Weeks

Re: Return a value for a Date Range

Anonymous — Fri, 12 Dec 2014 16:36:08 GMT

What is the the full load command ?

Re: Return a value for a Date Range

Fri, 12 Dec 2014 16:47:52 GMT

LOAD

Payment_Type

Promotion_Start_Date,
Date# (Promotion_Start_Date, 'MM/DD/YYYY') as DAY_COD,
if([Promotion_Start_Date] <= today()+21 and [Payment_Type] = 'Off Invoice','PENDING') as OI_Active_Within_3_Weeks

FROM

\\file on server

Re: Return a value for a Date Range

Anonymous — Fri, 12 Dec 2014 16:53:07 GMT

the Promotion_Start_Date on if must on Date Format.

if(Date# (Promotion_Start_Date, 'MM/DD/YYYY') <=.....)

Re: Return a value for a Date Range

maxgro — Fri, 12 Dec 2014 16:58:55 GMT

maybe you have to Date# also in the if

LOAD

Promotion_Start_Date,
Date# (Promotion_Start_Date, 'MM/DD/YYYY') as DAY_COD,
if( (Date# (Promotion_Start_Date, 'MM/DD/YYYY') <= (today()+21)) and [Payment_Type] = 'Off Invoice','PENDING') as OI_Active_Within_3_Weeks

Re: Return a value for a Date Range

MarcoWedel — Fri, 12 Dec 2014 17:04:02 GMT

LOAD *,
     If(DAY_COD <= Today()+21 and [Payment_Type] = 'Off Invoice','PENDING') as OI_Active_Within_3_Weeks;
LOAD Promotion_Start_Date, 
     Date# (Promotion_Start_Date, 'MM/DD/YYYY') as DAY_COD
FROM yoursource;

Re: Return a value for a Date Range

Fri, 12 Dec 2014 17:34:26 GMT

OK adding the Date # got me to the point that I can produce an = in the today().

But how do I script it to tag everything from today out to 21 days. Is there a way to say between today() and today()+21 ? Thank you for your help a head of time.

if( (Date# (Promotion_Start_Date, 'MM/DD/YYYY') = (today()+21)) and [Payment_Type] = 'Off Invoice','PENDING') as OI_Active_Within_3_Weeks,

Re: Return a value for a Date Range

MarcoWedel — Fri, 12 Dec 2014 17:36:00 GMT

LOAD *,
     If(DAY_COD >= Today() and DAY_COD <= Today()+21 and [Payment_Type] = 'Off Invoice','PENDING') as OI_Active_Within_3_Weeks;
LOAD Promotion_Start_Date,
     Date# (Promotion_Start_Date, 'MM/DD/YYYY') as DAY_COD
FROM yoursource;

Re: Return a value for a Date Range

Fri, 12 Dec 2014 21:09:48 GMT

OK with everyone's help I figured it out, it was a combination of the two.

if( (Date# (**Date Field**, 'MM/DD/YYYY') >= (today())) and (Date# (**Date Field**, 'MM/DD/YYYY') <= (today()+21)) and [**Second Condition**] = '**VALUE**','**RETURN VALUE**') as **New Field Name**

Thanks for your help!