Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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, ...
Hi
Please post a sample QVW document and sample data to allow more specific and accurate assistance.
Jonathan
Hi,
Can you post complete script??
simple left join must work
Regards
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;
It should be:
Left Join("Table 1")
Load Employee, Period, Bench as Bench_FTE
Resident "Table 2";
Michael,
It gives me the same result
Impossible... I'll try later.
It works - see attached. You're missing something...
I guess I have problems with Data format...
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.