Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following in my load script:
[table x]:
Load
Date,
Month,
Year,
[Year & Month]
From location x;
[table y]:
Load
Date,
Orders,
Revenue,
Salesperson
From location y;
// I need to left join the next table to table y because [Hours Worked] is at month level not Date level
[table z]:
Load
Salesperson,
[Hours worked],
[Year & Month],
[Year & Month] & Salesperson as [Hours Key]
From location z;
-----------------------------------------------------------------------------------------------
So, I need to add the [Hours Key] dimension to table y to make the left join work but [Year & Month] is in a different table.
I think I need to do a resident load but I'm not sure how this works.
Please can someone advise?
Many thanks.
Hi APS,
Here is a posible solution I think.
//=======================================
Table_y:
//[Orders]:
LOAD
Date,
Orders,
Revenue,
Salesperson
From [Location Y.qvd] (qvd);
//=======================================
// APS - I need to left join the next table to table y because [Hours Worked] is at month level not Date level
// [Greenee] - I don’t think you should join it, but concatenate it instead.
Concatenate (Table_y)
LOAD Date(MakeDate(Left(YearMonth,4),Right(YearMonth,2),28),'DD/MM/YYYY') AS [Date]
//this will Make a date for all the hours worked in that month registered on the 28th of each month
,Salesperson
,HoursWorked
,HoursKey
//,YearMonth // you dont need this anymore as it will come from the Calendar
From [Location Z.qvd] (qvd);
//=======================================
// Load the dates after
Table_x:
//[Calendar]:
LOAD
Date,
Month,
Year,
YearMonth
From [Location X.qvd] (qvd);
//=======================================
I have attached a small txt file with the test data that will produce the tables below.
Hope this helps
Good luck
Regards
Greenee
I'll give it a try. Thanks.