topic Re: Problem with Joins in QlikView

Problem with Joins

Fri, 20 Mar 2015 13:10:38 GMT

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

Re: Problem with Joins

jonathandienst — Fri, 20 Mar 2015 13:14:22 GMT

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

Jonathan

Re: Problem with Joins

PrashantSangle — Fri, 20 Mar 2015 13:16:00 GMT

Hi,

Can you post complete script??

simple left join must work

Regards

Re: Problem with Joins

Fri, 20 Mar 2015 13:21:22 GMT

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;

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;

Load Employee as Name ,Period,Bench

Resident Bench;

Drop Table Bench;

Left Join(Accounts)

Load Name as Employee, Bench as Bench_FTE

Resident 1;

Re: Problem with Joins

Anonymous — Fri, 20 Mar 2015 13:22:23 GMT

It should be:

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

Re: Problem with Joins

Fri, 20 Mar 2015 13:24:41 GMT

Michael,

It gives me the same result

Re: Problem with Joins

Anonymous — Fri, 20 Mar 2015 13:32:40 GMT

Impossible... I'll try later.

Re: Problem with Joins

Anonymous — Fri, 20 Mar 2015 13:48:50 GMT

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

Re: Problem with Joins

Fri, 20 Mar 2015 15:11:17 GMT

I guess I have problems with Data format...

Re: Problem with Joins

Anonymous — Fri, 20 Mar 2015 15:41:18 GMT

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.

Re: Problem with Joins

Peter_Cammaert — Fri, 20 Mar 2015 16:12:02 GMT

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

Re: Problem with Joins

Anonymous — Fri, 20 Mar 2015 16:22:46 GMT

Exactly.

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

Re: Problem with Joins

Fri, 20 Mar 2015 16:23:53 GMT

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.

Re: Problem with Joins

Anonymous — Fri, 20 Mar 2015 16:28:19 GMT

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

Re: Problem with Joins

Fri, 20 Mar 2015 16:35:07 GMT

It's working!!)

Re: Problem with Joins

Fri, 20 Mar 2015 16:35:46 GMT

it's String