Problem with Joins

[#1]

Hi, guys!

I have a problem here with Joins.

This is my first Table:

Employee	Account	Period	DataAccount
Pavel Kovalski	International	Feb-14	0
Pavel Kovalski	Bank	Mar-14	0
Pavel Kovalski	Other	Apr-14	0

This is my second table:

Employee	Period	Bench
Pavel Kovalski	Feb-14	0
Pavel Kovalski	Mar-14	1
Pavel Kovalski	Apr-14	1

I need to join them, so that I'll get a table with Employee, Account, Period , DataAccount and Bench.

I make it like:

Left Join(Table 1)
Load  Employee, Bench as Bench_FTE
Resident 1;

But all I get is :

Employee	Account	Period	Bench_FTE	DataAccount
Pavel Kovalski	International	Feb-14		0
Pavel Kovalski	International	Feb-14		0
Pavel Kovalski	Bank	Mar-14	1	0
Pavel Kovalski	Bank	Mar-14		1,00
Pavel Kovalski	Other	Apr-14	1	0
Pavel Kovalski	Other	Apr-14		1,00

[Peter_Cammaert · Accepted solution]

Or change the overly complex calculation of the Period column in Accounts and Bench into something like:

...date(MonthStart(date#(Period, 'DDMMYY')), 'MMM YY') AS Period, ...

[Anonymous]

Exactly.

Date formatting is easier with the date#() function.  And, MonthStart() here is necessary for the Period match.

[#4]

Guys,

I checked, there is no day in the date format, I used the previous code that Peter gave me. I think the problem is not in the date format.

However, while using Left join I have duplicate values.

[Anonymous]

What is the original format of your Month and Period fields?

[#6]

It's working!!)

[#7]

it's String