Left join help

[mahitham]

Hi Experts,

Can any one please help me on below issue.

I have Two tables like below. Here I am doing left join with DATE field.

While doing left join I am getting -129 error its like timeout error. After refreshing the browser also getting same error.

When I do the outer join its loading, but I need to combine these two tables into one.

Please suggest me to combine these two tables into one instead of left join with apply map or any other way.

Table1:

Date(Date#(Date(Timestamp(Date),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

ID,

CURRENCY,

RATE,

VALUE

From ....      

left join(Table1)

Table2:

Date(Date#(Date(Timestamp(STARTDATE),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

FROM_CCY,

TO_CCY

From ....  

Thanks in advance

[shiveshsingh]

I can see two common fields in your both the tables, DATE and From.

are you joining on both these points? What is the requirement from the second table.

[mahitham]

Hi Shivesh,

The common field between two tables is only DATE field.

From is command with table name not field.

I am trying to do left join these two tables by DATE field. Here Table1 is the Fact table.

Table2 is the dimension table. From second table DATE related FROM_CCY Need to get after joining with table1.

Can avoid TO_CCY field.

Thanks in advance.

[shiveshsingh]

Try this

Table1: load

Date(Date#(Date(Timestamp(Date),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

ID,

CURRENCY,

RATE,

VALUE

From ....     ;

Table2: mapping load

Date(Date#(Date(Timestamp(STARTDATE),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

FROM_CCY,

From ....  ;

Final:

load ApplyMap('Table2',DATE) as FROM_CCY,ID,

CURRENCY,

RATE,

VALUE

Resident Table1;

drop table Table1;

[mahitham]

Hi Shivesh,

Thanks for your reply.

When we do apply map do we get all the date fields from the fact table or only common dates between two tables. Please confirm.

[PrashantSangle]

can you share sample data??

Regards,

[shiveshsingh]

Hi

You'll get all the data from table1 and corresponding FROM_CCY from second table.

incase From_CCY is not present for some data in table 2, you can define NA like ApplyMap('Table2',DATE,'NA')

[YoussefBelloum]

Hi,

one question, why do you TimeStamp your date field and Date it again ?

...Date(Timestamp(Date),'DD/MM/YYYY')... ?

[mahitham]

Hi Youssef,

My date field is in DD/MM/YYYY hh:mm:ss format

to convert it to DD/M//YYYY I have used the below expression

Date(Date#(Date(Timestamp(STARTDATE),'DD/MM/YYYY'),'DD/MM/YYYY'),'DD/MM/YYYY') as DATE,

Please let me know am I did anything wrong here

Thanks in advance.

[Anil_Babu_Samineni]

Simple do this?

Date(Date#(STARTDATE, 'DD/MM/YYYY hh:mm:ss'), 'DD/M/YYYY') as DATE

OR

Date(STARTDATE, 'DD/M/YYYY') as DATE