How to get this expected output in a varaible.

[qlikviewforum]

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.

[dmac1971]

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.

[qlikviewforum]

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.

[dmac1971]

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.

[qlikviewforum]

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;

[qlikviewforum]

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?

[sasikanth]

hi

Try to use this

Test1:

Load  Monthname(date(TestDate,'YYYYMMMM')) as Test1 Resident Test;

Drop table test

[qlikviewforum]

I got the distinct value using TEXT(TestDate) as Test1

[Digvijay_Singh]

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