loading dates greater than specific value

[marikabi]

Hello guys,

I need an help in modifying the loading script.

What I am trying to do is to load only the data starting from 2016.

I have a column called [Period Name] where the dates are in this format : yyyy-mm-dd

For each record, the value in day is 01.

For example, for November 2016 I have : 2016-11-01

I have data also about 2015 and 2014.

How can I modify the script to load only the required data?

The piece of script for this part looks like:

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY
FROM
(ooxml, embedded labels, table is Data)
WHERE (Account='522110') or (Account='522120') or (Account='522210') or (Account='522220') or (Account='522410') ;

Could you please help me?

Many thanks.

Marika

[MK9885]

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

    MAPPING LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3)  as Quarter

    AUTOGENERATE (12);

      varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD'));

//      varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));

      varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

TempCalendar:

    LOAD

                  $(varMinDate) + Iterno()-1 as Num,

                  Date($(varMinDate) + IterNo() - 1) as TempDate

                  AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar:

LOAD

  *,

  [Quarter Number]-1 as PQ,

  if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,

  //If(Year<=$(vLast5Year),Year,If(Year>=$(vLast5Year),Year )) as Years,

// If (Year >= $(vLast5Year), Quarter) as Quarter_5Year,

// If (Year >= $(vLast5Year), Month) as Month_5Year,

  AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

  Min([Effective Date]) as MINN,

  AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load

  trim(date(TempDate,'YYYYMMDD')) as [DateID],

  date(TempDate,'MM/DD/YYYY') as [Effective Date],

  if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

    day(TempDate) as Day,

    TempDate as [US Calendar Format],

    date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],

    date(TempDate,'WWWW') as [Full Day Name],

    year(TempDate) as Year,

    inyear(TempDate,today(),0) * -1  as [CY],    // Current Year

    inyear(TempDate,today(),-1) * -1 as [First PY],

    inyear(TempDate,today(),-2) * -1 as [Second PY],

    inyeartodate(TempDate,today(),0) * -1  as [CYTD],

    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

      if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,

if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,

if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,

if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

      ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)  as [Quarter Year],

    inquarter(TempDate,today(),0) * -1 as [CQ],  // Current Quarter

    //    if(InQuarter(TempDate, today(),-1), 1, 0) as [PQ1],  // Previous Quarter

    inquarter(TempDate,today(),-4) * -1 as [First PQ],

    inquarter(TempDate,today(),-8) * -1 as [Second PQ],

    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

    date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,               

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

  date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

  date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

    week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate)  as [Week Month],

    weekDay(TempDate) as [Week Day],

    If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12],

    If( TempDate > monthstart(addmonths(today(),-2)) and TempDate <= today(),1) as [Rolling 3]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table  QuarterNAME;

Load the above script and name the Tab as Master Calendar.

and in your source date

SourceDate:

Load

*,

trim(date(MM/YY,'YYYYMMDD')) as [DateID],  //////Used as a key to join Master Calendar

Load

......

.........

.............

From....

varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD')); This will generate dates only from 2016. (if that's what you're looking for?)

If possible can you upload the same data file? And format of dates can be changed as we like.

[sunny_talwar]

Yes something is not right here.... seems like one Excel might have read the Dates as dates, but not the 2nd one.... I think you will need to use Date#() function for the Excel where date is not read and understood as date

[marikabi]

Hi Sunny,

I have checked more times on the excel, but both the files use the "Date" format.

And I am also wondering that if it wasn't the same format, I couldn't compare the costs from the two files in the same periods, right?

Thank you

[marikabi · Accepted solution]

Hi All,

thank for your support.

I don't know why, but the only way the year function worked was copying-pasting the dates as value.

This is crazy, but it worked

[MarcoWedel]

please post a small sample of your source excel file(s).

thanks

regards

Marco

[marikabi]

Hi Marco,

this is the file I am talking about:

Account	Period Name	EURO NET	Country
517110	2014-01-01	0.11631	Austria

Of course there are a lot of more columns, but I don't need them for my analysis (we are talking of about 190 columns for 50k rows.. the application would be too heavy).

The column Period name is formatted as date..

Now it is working fine

sorry but I can't post the original excel file ..