JOIN Alternative?

Thu, 11 Aug 2011 21:07:13 GMT

Hello...I'm doing an outer join with the sole purpose of adding a single field to each row of data. I load a single data file, where each row has a feeddate field attached to it - 12/1/2008, 1/1/2009, 2/1/2009, etc. For each row, there are other identifying fields such as business description fields, gender, ethnicity to name a few. There are also counter fields. The one I am working with for this issue is DS_Count. For each row, I'm trying to bring the prior period (feeddate) DS_Count. I've accomplished this by dumping the data into a temp file and adding 1 month to feeddate (addmonths(feeddate,1)) and then doing an outer join between the original data and this temp table. However, it seems very inefficient as it has doubled the size of my temp tables and increased the processing time of my load script. Is there a better way to accomplish the same goal? Thanks in advance.

LOAD * INLINE [
OMITGROUP, OMIT
group1, Name
];

Section Application;

SET QVDFILE= 'HRMetrics.qvd';
LET datatable='MasterTable';

LET vQvdFileName = peek( 'qfn', 0 );
LET vSrcFileName = peek( 'fn', 0 );
LET vSheetName = peek( 'sheetname', 0 );
LET vSource = peek( 'tsrc', 0 );
    LET vEOY_Count = peek( 'teoyc', 0 );
    LET vCurrent_Count = peek( 'tcc', 0 );
LET vFeedDate = peek( 'tfd', 0 );

//NullAsValue * ;
//SET NullInterpret='blank';

// Determine the source file - QVD or CSV
if FileSize ('All_Citi_Agg.qvd') > 0 then

// Load from QVD file:
MasterTable:
LOAD
feeddate
,DS_Count
,FTE_VALUE
,EXTENDED_WORKFORCE_EQUIV
,Hires
,NEW_HIRE_TYPE
,Terms
,NEW_TERM_TYPE
,Term_Reason
,Ethnic_Grp
,Gender
,Rating

,Report_Grouping
,Business
,OT_Report_Grouping
,OT_Business

,Region
,MANAGED_COUNTRY_ID
,MANAGED_COUNTRY
,Cntry_descr
,CGH_GBL_FUNCTN_ID
,[Sub-Bus_Level_0_Node]
,[Sub-Business_Level_0]
,[Sub-Bus_Level_1_Node]
,[Sub-Business_Level_1]
,[Sub-Bus_Level_2_Node]
,[Sub-Business_Level_2]
,[Sub-Bus_Level_3_Node]
,[Sub-Business_Level_3]
,[Sub-Bus_Level_4_Node]
,[Sub-Business_Level_4]
,[Sub-Bus_Level_5_Node]
,[Sub-Business_Level_5]
,[Sub-Bus_Level_6_Node]
,[Sub-Business_Level_6]
,[Sub-Bus_Level_7_Node]
,[Sub-Business_Level_7]
,Work_City
,Work_State
,PHYSICAL_REGION
,CGH_SERVICE_YRS

,Month
,Qtr
,CGH_OFFCR_TTL_DESC
,STD_GRADE
,[Non-Officer_Level_For_Rpt]
,[Non-Officer_Officer]
,Yrs_of_Service

,Q1_FORECAST_DS
,Q1_FORECAST_FTE
,Q2_FORECAST_DS
,Q2_FORECAST_FTE
,Q3_FORECAST_DS
,Q3_FORECAST_FTE
,Q4_FORECAST_DS
,Q4_FORECAST_FTE

//// CITY_COUNTRY_CONCAT_HML
//Work_City_EW & PHYSICAL_COUNTRY_DESC_EW as CITY_COUNTRY_CONCAT_HML

//FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
FROM [All_Citi_Agg.qvd] (qvd)

where (feeddate = '5/1/2011') or (feeddate = '6/1/2011') or (feeddate = '7/1/2011')
;

else

// Load from CSV file:
MasterTable:
LOAD
Period as feeddate
,DS_Count
,FTE_VALUE
,EXTENDED_WORKFORCE_EQUIV
,Hires
,NEW_HIRE_TYPE
,Terms
,NEW_TERM_TYPE
,Term_Reason
,Ethnic_Grp
,Gender
,Rating

,MS_Citi_Sum as Report_Grouping
,MS_Citi_Detail as Business
,MS_OT_Sum as OT_Report_Grouping
,MS_OT_Detail as OT_Business

,Region
,MANAGED_COUNTRY_ID
,MANAGED_COUNTRY_DESC as MANAGED_COUNTRY
,Cntry_descr
,CGH_GBL_FUNCTN_ID
,Sub_Bus_Level_0_Node as [Sub-Bus_Level_0_Node]
,Sub_Business_Level_0 as [Sub-Business_Level_0]
,Sub_Bus_Level_1_Node as [Sub-Bus_Level_1_Node]
,Sub_Business_Level_1 as [Sub-Business_Level_1]
,Sub_Bus_Level_2_Node as [Sub-Bus_Level_2_Node]
,Sub_Business_Level_2 as [Sub-Business_Level_2]
,Sub_Bus_Level_3_Node as [Sub-Bus_Level_3_Node]
,Sub_Business_Level_3 as [Sub-Business_Level_3]
,Sub_Bus_Level_4_Node as [Sub-Bus_Level_4_Node]
,Sub_Business_Level_4 as [Sub-Business_Level_4]
,Sub_Bus_Level_5_Node as [Sub-Bus_Level_5_Node]
,Sub_Business_Level_5 as [Sub-Business_Level_5]
,Sub_Bus_Level_6_Node as [Sub-Bus_Level_6_Node]
,Sub_Business_Level_6 as [Sub-Business_Level_6]
,Sub_Bus_Level_7_Node as [Sub-Bus_Level_7_Node]
,Sub_Business_Level_7 as [Sub-Business_Level_7]
,Work_City
,Work_State
,PHYSICAL_REGION
,CGH_SERVICE_YRS

,Month
,Qtr
,CGH_OFFCR_TTL_DESC
,STD_GRADE
,Non_Officer_Level_For_Rpt as [Non-Officer_Level_For_Rpt]
,Non_Officer_Officer as [Non-Officer_Officer]
,Yrs_of_Service

,Q1_FORECAST_DS
,Q1_FORECAST_FTE
,Q2_FORECAST_DS
,Q2_FORECAST_FTE
,Q3_FORECAST_DS
,Q3_FORECAST_FTE
,Q4_FORECAST_DS
,Q4_FORECAST_FTE

//// CITY_COUNTRY_CONCAT_HML
//Work_City_EW & PHYSICAL_COUNTRY_DESC_EW as CITY_COUNTRY_CONCAT_HML

//FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq) ;

// Create the QVD file:
STORE MasterTable INTO All_Citi_Agg.qvd;

end if

MasterTable3:
LOAD
DS_Count as DS_Count_Prior
,addmonths(feeddate,1) as feeddate

,Ethnic_Grp
,Gender
,Rating

,Report_Grouping
,Business
,OT_Report_Grouping
,OT_Business

,Region
,MANAGED_COUNTRY_ID
,MANAGED_COUNTRY
,Cntry_descr
,CGH_GBL_FUNCTN_ID
,[Sub-Bus_Level_0_Node]
,[Sub-Business_Level_0]
,[Sub-Bus_Level_1_Node]
,[Sub-Business_Level_1]
,[Sub-Bus_Level_2_Node]
,[Sub-Business_Level_2]
,[Sub-Bus_Level_3_Node]
,[Sub-Business_Level_3]
,[Sub-Bus_Level_4_Node]
,[Sub-Business_Level_4]
,[Sub-Bus_Level_5_Node]
,[Sub-Business_Level_5]
,[Sub-Bus_Level_6_Node]
,[Sub-Business_Level_6]
,[Sub-Bus_Level_7_Node]
,[Sub-Business_Level_7]
,Work_City
,Work_State
,PHYSICAL_REGION
,CGH_SERVICE_YRS

,Month
,Qtr
,CGH_OFFCR_TTL_DESC
,STD_GRADE
,[Non-Officer_Level_For_Rpt]
,[Non-Officer_Officer]
,Yrs_of_Service

resident MasterTable
;

outer join (MasterTable)
load *
resident MasterTable3
;

topic JOIN Alternative? in QlikView

JOIN Alternative?