topic Re: problem using RESIDENT load in QlikView

problem using RESIDENT load

Thu, 18 Oct 2012 09:26:01 GMT

I have a problem using RESIDENT load

I have to sources:

Excel file that contain “Serial Number”
Fact table that I load from sql server that contain all the “Serial Number”.

I want to load the Fact table from sql server but only the relevant “Serial Number” that appears in the Excel file.

What I did is this:

In the First tab, I load the data from Excel file into Failures table

Failures:
Directory;
LOAD Serial_Number as Serial_Number_Failure,
Name as Failure_Name
FROM
[Data\Jeppesen_data.xlsx]
(ooxml, embedded labels, table is Failures);

In the second tab I load the Fact table from sql server into Serial_Number_Base_Monthly_TMP table( Since I do not know how to restrict the sql for the relevant “Serial Number”, so I load all to a table(TMP).

Serial_Number_Base_Monthly_TMP:

LOAD "Serial_Number",
Status,
Ownership;

SQL SELECT *
FROM EPICPDW.dbo.Installed_Base_Monthly a
where a.Status ='ACTIVE'
and a.SnapShot_Date = (select MAX(b.SnapShot_Date)
from EPICPDW.dbo.Installed_Base_Monthly b);

In the third tab I used Resident ,in order to get only the relevant “Serial Number”.

Serial_Number_Base_Monthly:
LOAD *
RESIDENT Installed_Base_Monthly_TMP
WHERE exists (Serial_Number_Failure, Serial_Number);

drop Table Installed_Base_Monthly_TMP;

After I load I did not found the new table made “Serial_Number_Base_Monthly”.

What did I do wrong?

thank you

Re: problem using RESIDENT load

swuehl — Thu, 18 Oct 2012 09:34:57 GMT

NEWTABLE:

LOAD * resident TABLE;

will always concatenate the records to the resident TABLE, not creating a new resident table, because QV will auto-concatenate tables with same number of fields and same field names.

You can prevent QV from doing so using

NEWTABLE:

NOCONCATENATE LOAD * resident TABLE;

Re: problem using RESIDENT load

Thu, 18 Oct 2012 09:43:30 GMT

Hi,

where did u create a table Installed_Base_Monthly_TMP?

I think that u write the script wrongly instead of

Serial_Number_Base_Monthly:
LOAD *
RESIDENT Serial_Number_Base_Monthly_TMP
WHERE exists (Serial_Number_Failure, Serial_Number);

Regards,

Kabilan K.

Re: problem using RESIDENT load

Thu, 18 Oct 2012 09:58:09 GMT

Thank you

Re: problem using RESIDENT load

swuehl — Thu, 18 Oct 2012 10:05:45 GMT

I think this should also work, so you probably don't t need the resident load at all:

LOAD "Serial_Number",
Status,
Ownership

WHERE exists(Serial_Number_Failure, Serial_Number)

;

SQL SELECT *
FROM EPICPDW.dbo.Installed_Base_Monthly a
where a.Status ='ACTIVE'
and a.SnapShot_Date = (select MAX(b.SnapShot_Date)
from EPICPDW.dbo.Installed_Base_Monthly b);

Re: problem using RESIDENT load

Thu, 18 Oct 2012 10:07:08 GMT

I will try it

Re: problem using RESIDENT load

Thu, 18 Oct 2012 10:41:30 GMT

It works

thnak you again