topic Re: help with resident load in QlikView

help with resident load

Wed, 22 Jun 2011 14:23:59 GMT

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.

Re: help with resident load

Thu, 30 Jun 2011 12:58:12 GMT

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

Re: help with resident load

Thu, 30 Jun 2011 13:21:52 GMT

I'll give it a try. Thanks.