Mapping

[#1]

hi all,

PFA

i have holiday Data of calender year state wise, like

Data:

LOAD State,

     HoliDay,

     Date(Dates,'DD-MM-YYYY') as Dates

FROM

then i Created  Dates of year  by following way

Date_genration:

LOAD  Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates

AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());

then i joined  these two tables

now i want check when their is holiday then which type it is like

first i want to check whether that is holiday or not by comparing with data stored above Data Table if yes

then i want to check its day type like weekday or weekend

suppose that i have holiday on 1st Jan  suppose it Monday  then its should have flag weekday

suppose that i have holiday on 1st Jan  suppose it Sunday  then its should have flag weekend

this way we will get three field in flag Holiday,Weekday,WeekEnd

i have tried but i can't succeed

thanks in Advance

[preminqlik]

hi

PFA

[rbecher]

Most simple variant:

Date_genration:
LOAD DISTINCT 
    Date(Dates,'DD-MM-YYYY') as Dates, 
    'Holiday' as Flag
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

LOAD *, if(WeekDay(Dates)<6, 'Weekday', 'Weekend') as Flag Where Not Exists(Dates);
LOAD
    Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates
AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());

- Ralf

[#4]

hi @prem u have not used what data i given for state holiday

[#5]

hi Ralf u have not used State holiday data for mapping

[rbecher]

What does it mean?

[rbecher]

Do you need a set of dates by state?

[#8]

yes i have attached state wise holiday list  please find excel file for that

i need this data state wise

[rbecher]

But what when Holiday is on two states? Do you need a full set of dates (a calendar) for all states?

[#10]

yes i need complete set of Data of states

consider that holiday according that state

[rbecher]

Here we go:

States:
LOAD DISTINCT 
    State as StateName
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

Let n=NoOfRows('States');

Holiday:
LOAD DISTINCT 
    State & '_' & Date(Dates,'DD-MM-YYYY') as StateHoliday
FROM fest.xlsx
(ooxml, embedded labels, table is Sheet2);

FOR i=0 to $(n)-1
    Let vState = peek('StateName', $(i), 'States');

    Dates:
    LOAD Dates, '$(vState)' as State, If(IsNull(Lookup('StateHoliday', 'StateHoliday', '$(vState)' & '_' & Date(Dates,'DD-MM-YYYY'), 'Holiday')), If(WeekDay(Dates)<6, 'Weekday', 'Weekend'), 'Holiday') as Flag;
    LOAD Date(Date#(20131231,'YYYYMMDD')+RecNo(),'DD-MM-YYYY') AS Dates
    AutoGenerate Ceil (YearEnd(Today())) -YearStart(Today());
NEXT

Drop Tables States, Holiday;

- Ralf