Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have something called as PROC_DATE which contains the latest date from a table.
Requirement:
If the PROC_DATE='09-MAR-16' then I need to check what is PROC_DATE-8(Excluding Saturday and Sunday). So here the PROC_DATE-8 will be '29-FEB-16' which falls in the month of FEB,2016. So in this scenario the expected output in the variable zNextDate should be zNextDate='2016_02','2016_03'. Since the PROC_DATE-8(Excluding Saturdat and Sunday) falls in different Month.
Another scenario:
If the PROC_DATE='16-MAR-16' then I need to check what is PROC_DATE-8(Excluding Saturday and Sunday). So here the PROC_DATE-8 will be '07-MAR-16' which falls in the month of MAR,2016. So in this scenario the expected output in the variable zNextDate should be zNextDate='2016_03'. Since the PROC_DATE-8(Excluding Saturdat and Sunday) falls in same Month.
Can someone please let me know.
Hope the requirement is understandble. If not please let me know.
Will this do for you? May need to add a check in to look if the day is on say a Friday and adjust? If this isn't right then update and will try and fix.
Thanks for your help. I am looking for solution in the backend.
We will get only one date from a table which is Max(PROC_DATE). Based on that we need to check PROC_DATE-8 excluding the weekends falls on which date. In this case it falls on 29-FEB-16. So the variable should have two months in the zNextDate='2016_03','2016_02'. If both the PROC_DATE and PROC_DATE excluding the weekends falls on the same month then it should have only one month in the variable (i,e) zNextDate='2016_03'.
Hope the requirement is understandable. If you have any more questions please let me know.
Same thing, or rather the same syntax. Updated so the work is done at load. Please check. As stated you may need to add in a check to see if the day is a weekday etc etc. Should be enough here to get you started.
I have achieved it using below code.
TMP:
LOAD Max(Date(DATE,'MM/DD/YYYY')) as MaxDate
FROM
(ooxml, embedded labels, table is Sheet1);
LET vMaxBusDate = Peek('MaxDate',0,'TMP');
//DROP Table TMP;
For i=0 to 9
Test:
Load Date(MaxDate,'YYYY_MM') as TestDate Resident TMP;
Concatenate
Load Date('$(vMaxBusDate)'-$(i),'YYYY_MM') as TestDate
AutoGenerate('$(i)')
where WeekDay('$(vMaxBusDate)'-$(i)) < 5;
next i;
drop table TMP;
NoConcatenate
Test1:
Load Distinct TestDate as Test1 Resident Test;
drop table Test;
Using the code which I am using I am not able to get the distinct months. (I,e) Value in the Test1 field should be 2016_03 and 2016_02.
Any idea how can I get only distinct values in Test1 field?
hi
Try to use this
Test1:
Load Monthname(date(TestDate,'YYYYMMMM')) as Test1 Resident Test;
Drop table test
I got the distinct value using TEXT(TestDate) as Test1
Try something like this -
Input:
Load Date(Max(Date#(PROC_DATE,'DD-MMM-YYYY')),'DD-MMM-YYYY') as PROC_DATE inline [
PROC_DATE
16-MAR-16 ];
Let vDateMax = Peek('PROC_DATE',0,'Input');//&'_'&Num(Month(Peek('PROC_DATE',0,'Input')));
Let vDateMax1YM = Year(vDateMax)&'_'&Num(Month(vDateMax));
Let vDate8 = FirstWorkDate(Peek('PROC_DATE',0,'Input'),8);
Let vDate8YM = Year(vDate8)&'_'&Num(Month(vDate8));
if vDateMax1YM = vDate8YM then
let zNextDate = vDate8YM;
else
Let zNextDate = chr(39)& vDate8YM & chr(39)& ',' & chr(39)& vDateMax1YM & chr(39);