Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help with resident load

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.

2 Replies
Not applicable
Author

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.

SalesPersonHours2.jpg

SalesPersonHours1.jpg

Hope this helps

Good luck

Regards

Greenee

Not applicable
Author

I'll give it a try. Thanks.