I am trying to load data using two cross tables.
When I load them separately, it loads with no issues. However, when I try to load them together, I get an error saying one of the resident tables do not exist. I tried switching the order of the data script and it always turns out that the latter data step results in error.
Here is the code, and I've also attached the QVF.
Leading_Index:
LOAD
"Date" as DATE,
US as [US_ld],
AL as [Alabama_ld],
AK as [Alaska_ld],
AZ as [Arizona_ld],
AR as [Arkansas_ld],
CA as [California_ld],
CO as [Colorado_ld],
CT as [Connecticut_ld],
DE as [Delaware_ld],
FL as [Florida_ld],
GA as [Georgia_ld],
HI as [Hawaii_ld],
ID as [Idaho_ld],
IL as [Illinois_ld],
"IN" as [Indiana_ld],
IA as [Iowa_ld],
KS as [Kansas_ld],
KY as [Kentucky_ld],
LA as [Louisiana_ld],
ME as [Maine_ld],
MD as [Maryland_ld],
MA as [Massachusetts_ld],
MI as [Michigan_ld],
MN as [Minnesota_ld],
MS as [Mississippi_ld],
MO as [Missouri_ld],
MT as [Montana_ld],
NE as [Nebraska_ld],
NV as [Nevada_ld],
NH as [New Hampshire_ld],
NJ as [New Jersey_ld],
NM as [New Mexico_ld],
NY as [New York_ld],
NC as [North Carolina_ld],
ND as [North Dakota_ld],
OH as [Ohio_ld],
OK as [Oklahoma_ld],
"OR" as [Oregon_ld],
PA as [Pennsylvania_ld],
RI as [Rhode Island_ld],
SC as [South Carolina_ld],
SD as [South Dakota_ld],
TN as [Tennessee_ld],
TX as [Texas_ld],
UT as [Utah_ld],
VT as [Vermont_ld],
VA as [Virginia_ld],
WA as [Washington_ld],
WI as [Wisconsin_ld],
WV as [West Virginia_ld],
WY as [Wyoming_ld]
FROM [lib://State Growth Indicators/coincident_leading index monthly tracker.xls]
(biff, embedded labels, table is Leading%_Qlik$);
Leading:
Load
DATE,
Sum([US_ld]) as [US],
Sum([Alabama_ld]) as [Alabama],
Sum([Alaska_ld]) as [Alaska],
Sum([Arizona_ld]) as [Arizona],
Sum([Arkansas_ld]) as [Arkansas],
Sum([California_ld]) as [California],
Sum([Colorado_ld]) as [Colorado],
Sum([Connecticut_ld]) as [Connecticut],
Sum([Delaware_ld]) as [Delaware],
Sum([Florida_ld]) as [Florida],
Sum([Georgia_ld]) as [Georgia],
Sum([Hawaii_ld]) as [Hawaii],
Sum([Idaho_ld]) as [Idaho],
Sum([Illinois_ld]) as [Illinois],
Sum([Indiana_ld]) as [Indiana],
Sum([Iowa_ld]) as [Iowa],
Sum([Kansas_ld]) as [Kansas],
Sum([Kentucky_ld]) as [Kentucky],
Sum([Louisiana_ld]) as [Louisiana],
Sum([Maine_ld]) as [Maine],
Sum([Maryland_ld]) as [Maryland],
Sum([Massachusetts_ld]) as [Massachusetts],
Sum([Michigan_ld]) as [Michigan],
Sum([Minnesota_ld]) as [Minnesota],
Sum([Mississippi_ld]) as [Mississippi],
Sum([Missouri_ld]) as [Missouri],
Sum([Montana_ld]) as [Montana],
Sum([Nebraska_ld]) as [Nebraska],
Sum([Nevada_ld]) as [Nevada],
Sum([New Hampshire_ld]) as [New Hampshire],
Sum([New Jersey_ld]) as [New Jersey],
Sum([New Mexico_ld]) as [New Mexico],
Sum([New York_ld]) as [New York],
Sum([North Carolina_ld]) as [North Carolina],
Sum([North Dakota_ld]) as [North Dakota],
Sum([Ohio_ld]) as [Ohio],
Sum([Oklahoma_ld]) as [Oklahoma],
Sum([Oregon_ld]) as [Oregon],
Sum([Pennsylvania_ld]) as [Pennsylvania],
Sum([Rhode Island_ld]) as [Rhode Island],
Sum([South Carolina_ld]) as [South Carolina],
Sum([South Dakota_ld]) as [South Dakota],
Sum([Tennessee_ld]) as [Tennessee],
Sum([Texas_ld]) as [Texas],
Sum([Utah_ld]) as [Utah],
Sum([Vermont_ld]) as [Vermont],
Sum([Virginia_ld]) as [Virginia],
Sum([Washington_ld]) as [Washington],
Sum([Wisconsin_ld]) as [Wisconsin],
Sum([West Virginia_ld]) as [West Virginia],
Sum([Wyoming_ld]) as [Wyoming]
Resident Leading_Index
Group by DATE ;
Drop Table Leading_Index;
CrossTable_5:
CrossTable(State, LeadIndx, 1)
Load *
Resident Leading;
Coincident_Index:
LOAD
"Date" as DATE,
US as [US_c],
AL as [Alabama_c],
AK as [Alaska_c],
AZ as [Arizona_c],
AR as [Arkansas_c],
CA as [California_c],
CO as [Colorado_c],
CT as [Connecticut_c],
DE as [Delaware_c],
FL as [Florida_c],
GA as [Georgia_c],
HI as [Hawaii_c],
ID as [Idaho_c],
IL as [Illinois_c],
"IN" as [Indiana_c],
IA as [Iowa_c],
KS as [Kansas_c],
KY as [Kentucky_c],
LA as [Louisiana_c],
ME as [Maine_c],
MD as [Maryland_c],
MA as [Massachusetts_c],
MI as [Michigan_c],
MN as [Minnesota_c],
MS as [Mississippi_c],
MO as [Missouri_c],
MT as [Montana_c],
NE as [Nebraska_c],
NV as [Nevada_c],
NH as [New Hampshire_c],
NJ as [New Jersey_c],
NM as [New Mexico_c],
NY as [New York_c],
NC as [North Carolina_c],
ND as [North Dakota_c],
OH as [Ohio_c],
OK as [Oklahoma_c],
"OR" as [Oregon_c],
PA as [Pennsylvania_c],
RI as [Rhode Island_c],
SC as [South Carolina_c],
SD as [South Dakota_c],
TN as [Tennessee_c],
TX as [Texas_c],
UT as [Utah_c],
VT as [Vermont_c],
VA as [Virginia_c],
WA as [Washington_c],
WI as [Wisconsin_c],
WV as [West Virginia_c],
WY as [Wyoming_c]
FROM [lib://State Growth Indicators/coincident_leading index monthly tracker.xls]
(biff, embedded labels, table is Coincident%_Qlik$);
Coincident:
Load
DATE,
Sum([US_c]) as [US],
Sum([Alabama_c]) as [Alabama],
Sum([Alaska_c]) as [Alaska],
Sum([Arizona_c]) as [Arizona],
Sum([Arkansas_c]) as [Arkansas],
Sum([California_c]) as [California],
Sum([Colorado_c]) as [Colorado],
Sum([Connecticut_c]) as [Connecticut],
Sum([Delaware_c]) as [Delaware],
Sum([Florida_c]) as [Florida],
Sum([Georgia_c]) as [Georgia],
Sum([Hawaii_c]) as [Hawaii],
Sum([Idaho_c]) as [Idaho],
Sum([Illinois_c]) as [Illinois],
Sum([Indiana_c]) as [Indiana],
Sum([Iowa_c]) as [Iowa],
Sum([Kansas_c]) as [Kansas],
Sum([Kentucky_c]) as [Kentucky],
Sum([Louisiana_c]) as [Louisiana],
Sum([Maine_c]) as [Maine],
Sum([Maryland_c]) as [Maryland],
Sum([Massachusetts_c]) as [Massachusetts],
Sum([Michigan_c]) as [Michigan],
Sum([Minnesota_c]) as [Minnesota],
Sum([Mississippi_c]) as [Mississippi],
Sum([Missouri_c]) as [Missouri],
Sum([Montana_c]) as [Montana],
Sum([Nebraska_c]) as [Nebraska],
Sum([Nevada_c]) as [Nevada],
Sum([New Hampshire_c]) as [New Hampshire],
Sum([New Jersey_c]) as [New Jersey],
Sum([New Mexico_c]) as [New Mexico],
Sum([New York_c]) as [New York],
Sum([North Carolina_c]) as [North Carolina],
Sum([North Dakota_c]) as [North Dakota],
Sum([Ohio_c]) as [Ohio],
Sum([Oklahoma_c]) as [Oklahoma],
Sum([Oregon_c]) as [Oregon],
Sum([Pennsylvania_c]) as [Pennsylvania],
Sum([Rhode Island_c]) as [Rhode Island],
Sum([South Carolina_c]) as [South Carolina],
Sum([South Dakota_c]) as [South Dakota],
Sum([Tennessee_c]) as [Tennessee],
Sum([Texas_c]) as [Texas],
Sum([Utah_c]) as [Utah],
Sum([Vermont_c]) as [Vermont],
Sum([Virginia_c]) as [Virginia],
Sum([Washington_c]) as [Washington],
Sum([Wisconsin_c]) as [Wisconsin],
Sum([West Virginia_c]) as [West Virginia],
Sum([Wyoming_c]) as [Wyoming]
Resident Coincident_Index
Group by DATE ;
Drop Table Coincident_Index;
CrossTable_4:
CrossTable(State, CoinIndx, 1)
Load *
Resident Coincident;
