problem using RESIDENT load

[#1]

Hi

I have a problem using RESIDENT load

I have to sources:

1. Excel file that contain “Serial Number”
2. 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:

1. 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);

1. 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);

1. 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

[swuehl · Accepted solution]

A

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;

[#3]

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.

[#4]

Thank you

[swuehl]

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);

[#6]

I will try it

[#7]

It works

thnak you again