Dealing with two dates

[anuradhaa]

I have below two dates and they are in two different tables, I want to use common date.

Than i use master calander as belwo,

[TempCalendar]:

LOAD DISTINCT

    DATE,   

  Year(DATE) as Year,

  Month(DATE) as Month,

  'Q' & Ceil(Month(DATE)/3) as AQuarter

  RESIDENT ABC_DATA;

join

LOAD DISTINCT

  DATE,

  Year(DATE) as Year,

  Month(DATE) as Month,

  'Q' & Ceil(Month(DATE)/3) as Quarter

RESIDENT Charges;

MainCalendar:

load DATE,

  Year,

  Month,

  Quarter

  Resident TempCalendar;

Here i remane ABC_DATE and XYZ_DATE when i lod that table.

But when i look at the table year,Month is missing in some recrds.

Please help

ABC_DATE	XYZ_DATE
5/30/2014 8:45	1/18/2014 23:50
5/30/2014 8:45	3/25/2014 3:25
5/30/2014 8:45	3/28/2014 10:11
5/30/2014 8:45	4/2/2014 13:46
5/30/2014 8:45	4/4/2014 9:45
5/30/2014 8:45	4/14/2014 10:04
5/30/2014 8:45	4/15/2014 3:00
5/30/2014 8:45	4/15/2014 6:20
5/30/2014 8:45	4/18/2014 8:49
5/30/2014 8:45	4/18/2014 21:05
5/30/2014 8:45	4/21/2014 13:46
5/30/2014 8:45	4/26/2014 6:25
5/30/2014 8:45	4/28/2014 10:30
5/30/2014 8:45	5/5/2014 9:57
5/30/2014 8:45	5/8/2014 1:20
5/30/2014 8:45	5/10/2014 1:20
5/30/2014 8:45	5/12/2014 10:37

[alexandros17]

In your main calendar you are referencing quarter (in the TempCalendar you called it AQuarter)

rename AQuarter in quarter and reload.

Let me know

[anuradhaa]

Thank You Alessandro,

But that is not the issue here. DAte is there in all raw, but year and moths are missing in some records

[alexandros17]

It is normal with a Join (outer) because you are taking sets of data from both tables and if a date (so year and month) is present in  a table and not in the other you will see dates but missing year and month for that date.

[Anonymous]

Anuradha,

Try concatenating instead of join.

[TempCalendar]:

LOAD DISTINCT

    DATE

RESIDENT ABC_DATA;

concatenate (TempCalendar)

LOAD DISTINCT

  DATE

RESIDENT Charges;

MainCalendar:

noconcatenate

load distinct DATE,

  Year(DATE) as Year,

  Month(DATE) as Month,

  'Q' & ceil(month(DATE)/3) as Quarter

Resident TempCalendar;

drop table TempCalendar;

You could also only load distinct once in main calendar as an alternative instead of both times in tempcalendar.

[anuradhaa]

The problem is i have two date formats,

5/30/2014  8:45:00 AM and   3/25/2014  3:25:00 AM. So is it a problem here?

[Anonymous]

looks like the same format to me

[marcus_malinow]

I'd suggest that in addition to concatenation, you change your dates in your source tables to date format rather than timestamps. If you are  working with a lot of data this will result in a far smaller calendar table.

[anuradhaa]

sorry  my two date formats are

2014-06-27 14:01 and 07-18-2014 07:10

[saikatghosh · Accepted solution]

istead of join concate two table and convert the date into proper date format by using floor

ie  Date(floor(Date)) as Date

Table1:

Load

Date(Floor(DATE) as DATE

Resident ABC_DATA;

Concatenate(Table1)

Load

Date(Floor(DATE) as DATE

Resident Charges;

Master_Calendar:

Load

DATE,

Year(DATE) as YEAR,

Month(DATE) as MONTH

Resident Master_Calendar;