Varchar date

[Mahamed_Qlik]

I have Year and month field in database which has datatype as varchar.
Year
2000

2010

2020

Month 

01

02

03

Now I have another date as custom_date in another table with the format as 

custom_date

1/02/1999 10:12:11 AM

11/15/2010 02:10:44 PM

Now I want connect above month and date to custom_date(month) & custom_date(year)

how I can achieve this.

I tried to change the format of first table year and month field as 

Date(Date#(Year,'YYYY'),'YYYY') As Year

Date(Date#(Month,'MM'),'MM') As  Month

but its not working
Kindly assist.

Regards

mahamed

[Or]

You won't be able to connect a timestamp to a month or year - you'll have to break the timestamp down in to parts.

Load custom_date, year(custom_date) as Year, month(custom_date) as Month

From YourTable;

or, if you prefer:

Load Year, Month, MakeDate(Year,Month) as MonthKey

From Table1;

Load custom_date, MonthName(custom_date) as MonthKey

From Table2;

[Mahamed_Qlik]

Hi Champion,

Thank you for your response.

I have already done this -

Load custom_date, year(custom_date) as Year, month(custom_date) as Month

From YourTable;

but Year and month generation from above script are not matching with the Year and month of the another table.

[Or]

Use Num#() or Num() on the varchar version to make sure they're parsed as a number, or use text() on the year() and month() values to evaluate as text. That will ensure that matching values are connected as keys. I'd recommend using the version with MonthName() / MakeDate() though, since that will connect the rows with a single-field key rather than two fields.