Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (1)
2 Replies
Not applicable

Re: help with resident load

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

Re: help with resident load

I'll give it a try. Thanks.