Solved: Re: Problem with Joins - Qlik Community

Report Inappropriate Content · ‎2015-03-20

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 · ‎2015-03-20

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, ...

View solution in original post

jonathandienst · ‎2015-03-20

Hi

Please post a sample QVW document and sample data to allow more specific and accurate assistance.

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

PrashantSangle · ‎2015-03-20

Hi,

Can you post complete script??

simple left join must work

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Report Inappropriate Content · ‎2015-03-20

Hi,Max

Here is my complete script:

Data:

CrossTable(Month, DataAccount, 11)

Load *

FROM

[Billable SWE1415.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Accounts:

load

Employee,

MonthName(Date(MakeDate(20&num#(Right(Month,2)),num#(Mid(Month,4,2)),num#(Left(Month,2))),'MMM.YY')) as Period,

num#(DataAccount,'#,##') as DataAccount,

Account

Resident Data;

Drop Table Data;

2:

CrossTable(Period, Bench, 10)

LOAD *

FROM

Bench_JanDec2015.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Bench:

Load Employee ,

MonthName(Date(MakeDate(20&num#(Right(Period,2)),num#(Mid(Period,4,2)),num#(Left(Period,2))),'MMM.YY')) as Period,

num#(Bench,'#,##') as Bench

Resident 2;

Drop Table 2;

1:

Load Employee as Name ,Period,Bench

Resident Bench;

Drop Table Bench;

Left Join(Accounts)

Load Name as Employee, Bench as Bench_FTE

Resident 1;

Anonymous · ‎2015-03-20

It should be:

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

Report Inappropriate Content · ‎2015-03-20

Michael,

It gives me the same result

Anonymous · ‎2015-03-20

Impossible... I'll try later.

Anonymous · ‎2015-03-20

It works - see attached. You're missing something...

Report Inappropriate Content · ‎2015-03-20

I guess I have problems with Data format...

Anonymous · ‎2015-03-20

Most likely. It looks the same 'Feb-14', but in fact it could be February 1, 2014 in one place but February 28, 2014 in another - no match. If you remove third parameter in your makedate() function, they all will be the 1st day of month and will match.