Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the the full load command ?
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
the Promotion_Start_Date on if must on Date Format.
if(Date# (Promotion_Start_Date, 'MM/DD/YYYY') <=.....)
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
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;
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,
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;
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!