Date

[sevvalk]

Hi,

I have a date field. This field is 'spsiht'. The format of the spsiht column looks like 'D.MM.YYYY hh:mm:ss'. I changed the spsiht to ref with date function. And i joined with Date column of Master Calender. But there was no match between the two columns.

I request your help.

[Aditya_Chitale]

make sure the date format in both tables is the same. Also check if date in master calendar is in text format or not.

Regards,

Aditya

[sevvalk]

It is my Master Calender script 

Let vMinDate=num('1.1.2022'); //min tarih seçilir.
Let vMaxDate=(num(Today())+365); //max tarih bugün takvim oluşturur.

CalenderTemp:
load $(vMinDate)+ IterNo()-1 as DATE
AutoGenerate(1) While $(vMinDate)+IterNo()-1<=$(vMaxDate);

[Master Calender_Hedef]:
Load
Date(DATE) as Date,
Date(DATE) as [AutoCalenderDate],
Year(DATE) as "Year",
Month(DATE) as "Month",
Num(Day(DATE)) as "Day Number",
Num(month(DATE)) as "Month Number",
Week(DATE) as "Week",
Dual('Q'&Num(Ceil(Num(Month(DATE))/3)),Num(Ceil(NUM(Month(DATE))/3),00)) as "Quarter",
Dual(Year(DATE)&' '&Num(Ceil(Num(Month(DATE))/3))&'.Quarter',QuarterStart(DATE)) as "Year-Quarter",
Date(MonthStart(DATE),'YYYY-MM') as "Year-Month",
Dual(Date(DATE,'WWWW'),WeekDay(DATE)) AS "Day"
Resident CalenderTemp;

Drop Table CalenderTemp;

[Aditya_Chitale]

from your screenshots it looks like date column names are different in both tables. in fact table it is "ref", but in master calendar it is "date". you need to have same column name in both tables.

Regards,

Aditya

[sevvalk]

I manually choose to join between ref and date.

[Aditya_Chitale]

I am not quiet getting your point. What do you mean by manually join ?

Regards,

Aditya

[sevvalk]

I used the "Concatenate or join" section in the data manager. Here, I chose left join as the join type and ref and date as the fields to be associated.

[sevvalk]

Let vMinDate=num('1.1.2022'); //min tarih seçilir.
Let vMaxDate=(num(Today())+365); //max tarih bugün takvim oluşturur.

CalenderTemp:
load $(vMinDate)+ IterNo()-1 as DATE
AutoGenerate(1) While $(vMinDate)+IterNo()-1<=$(vMaxDate);

[Master Calender_Hedef]:
Load
Date(DATE) as Date,
Date(DATE) as [AutoCalenderDate],
Year(DATE) as "Year",
Month(DATE) as "Month",
Num(Day(DATE)) as "Day Number",
Num(month(DATE)) as "Month Number",
Week(DATE) as "Week",
Dual('Q'&Num(Ceil(Num(Month(DATE))/3)),Num(Ceil(NUM(Month(DATE))/3),00)) as "Quarter",
Dual(Year(DATE)&' '&Num(Ceil(Num(Month(DATE))/3))&'.Quarter',QuarterStart(DATE)) as "Year-Quarter",
Date(MonthStart(DATE),'YYYY-MM') as "Year-Month",
Dual(Date(DATE,'WWWW'),WeekDay(DATE)) AS "Day"
Resident CalenderTemp;

Drop Table CalenderTemp;

LIB CONNECT TO 'LoginX (abc)';
fg_satsps:
LOAD spsbln,
spsdrm,
date(spsiht) as Date;
SQL SELECT spsbln,
spsdrm,
spsiht
FROM informix.fg_satsps;

It doesn't work.