Month

[qlikview979]

Hi Experts,

I have in  date in Excel like

STARTDATE  ENDDATE

27/03/2016      31/05/2016

I need output like

[Anil_Babu_Samineni]

Follow this.

I just added Years if you want to add Months please change that and let me know

- Anil Chowdary

[yadav_anil782]

Hi Mahesh ,

if your dates are missing then you have to find missing dates between min and max date with loop and min - max variable - this link will help to understand this proccess -  Creating A Master Calendar

1. Temp: 
2. Load 
3.                min(date) as minDate, 
4.                max(date) as maxDate 
5. Resident table; 
6.  
7. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
8. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
9. DROP Table Temp; 
10.  
11. TempCalendar: 
12. LOAD 
13.                $(varMinDate) + Iterno()-1 As Num, 
14.                Date($(varMinDate) + IterNo() - 1) as TempDate 
15.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

[qlikview979]

Hi Anil

I Don't want Missing Dates  This is my input:-

I have in  date in Excel like

STARTDATE  ENDDATE

27/03/2016      31/05/2016

Here April month data is not there but i need output given below like this.

Expected output:-

Date:-

2016_Mar

2016_Apr

2016_May

[varshavig12]

For End Date

=date(date#(ENDDATE,'DD/MM/YYYY'),'YYYY')&'_'&date(date#(ENDDATE,'DD/MM/YYYY'),'MMM')

[varshavig12]

Output:

End_date:

=date(date#(ENDDATE,'DD/MM/YYYY'),'YYYY')&'_'&date(date#(ENDDATE,'DD/MM/YYYY'),'MMM')

Start_date:

=date(date#(STARTDATE,'DD/MM/YYYY'),'YYYY')&'_'&date(date#(STARTDATE,'DD/MM/YYYY'),'MMM')

[qlikview979]

Hi Varsha,

I done like this

DATE(Date#(STARTDATE,'DD/MM/YYYY'),'YYYY_MMM') as STARTDATE,

DATE(Date#(ENDDATE,'DD/MM/YYYY'),'YYYY_MMM') AS ENDDATE,

I got like this

STARTDATE  ENDDATE

2016_Mar        2016_May

Here I don't have April data I want April month also below like this.

But I need output :-

Date:-

2016_Mar,

2016_Apr,

2016_May

Regards,

Mahesh

[Anil_Babu_Samineni]

Mahesh,

Did you try my attachment.

[varshavig12]

I'm sorry.

I missed out April.

I will try and let you know

[Digvijay_Singh]

Not sure if you just want this for representation of these two dates only -

[Anonymous]

Hi Mahesh,

See this code:

Data:

LOAD *,

    Date(DateInc)    as Date;

LOAD * INLINE [

    DateInc, Value

    01/05/2016, 10

    01/06/2016, 20

    01/08/2016, 50

];

LEFT JOIN

LOAD

    Date,

    If(IsNull(Previous(Date)),'31/12/2016',

        Previous(Date)-1) as PrevDate

RESIDENT Data

ORDER BY Date DESC;

Calendariced:

Load *,

    Month(Date)        as Month,

    Year(Date)        as Year,

    MonthName(Date) as MonthYear;   

LOAD 

    Value,

    Date + Iterno()-1 As Num,

    Date(Date + IterNo() - 1) as Date 

RESIDENT Data

While Date + IterNo() -1 <= PrevDate;       

DROP TABLE Data;

EXIT Script;

Regards!