topic Dealing with two dates in QlikView

Dealing with two dates

anuradhaa — Mon, 21 Jul 2014 09:25:39 GMT

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

Re: Dealing with two dates

alexandros17 — Mon, 21 Jul 2014 09:40:47 GMT

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

rename AQuarter in quarter and reload.

Let me know

Re: Dealing with two dates

anuradhaa — Mon, 21 Jul 2014 09:43:20 GMT

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

Re: Dealing with two dates

alexandros17 — Mon, 21 Jul 2014 09:58:49 GMT

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.

Re: Dealing with two dates

Anonymous — Mon, 21 Jul 2014 10:12:06 GMT

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.

Re: Dealing with two dates

anuradhaa — Mon, 21 Jul 2014 10:15:23 GMT

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?

Re: Dealing with two dates

Anonymous — Mon, 21 Jul 2014 10:19:57 GMT

looks like the same format to me

Re: Dealing with two dates

marcus_malinow — Mon, 21 Jul 2014 10:27:58 GMT

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.

Re: Dealing with two dates

anuradhaa — Mon, 21 Jul 2014 10:34:55 GMT

sorry my two date formats are

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

Re: Dealing with two dates

saikatghosh — Mon, 21 Jul 2014 10:43:32 GMT

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;

Re: Dealing with two dates

Anonymous — Mon, 21 Jul 2014 10:43:47 GMT

i agree with marcus.

you could use this to change it to date and eliminate the time data portion unless it is essential (alternatively have another time only table)

2014-06-27 14:01 use date(timestamp#(DATE, 'YYYY-MM-DD hh:mm')

07-18-2014 07:10 use date(timestamp#(DATE, 'MM-DD-YYYY hh:mm')

Re: Dealing with two dates

marcus_malinow — Mon, 21 Jul 2014 10:48:01 GMT

This shouldn't be a problem if you use the ALT function to interpret your dates in the tables you first load the dates into.

You could use something like

Date(ALT(timestamp#(Date, 'YYYY-MM-DD hh:mm'), timestamp(Date('MM-DD-YYYY hh:mm'))