Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Qlik (by default) tries to concatenate data tables with identical fields list (looks like your case).
I am not 100% sure what are you trying to achieve in your script, but you can use "noconcatenate" statement before LOAD or rename your fields or use QUALIFY statement...
One of these options will resolve your issue and you will see the second table available...
Regards,
Vlad
Qlik (by default) tries to concatenate data tables with identical fields list (looks like your case).
I am not 100% sure what are you trying to achieve in your script, but you can use "noconcatenate" statement before LOAD or rename your fields or use QUALIFY statement...
One of these options will resolve your issue and you will see the second table available...
Regards,
Vlad
Add NoConcatenate after load statement of Leading_Index and before load statement of Leading , similarly add NoCONCATENATE between Coincident_Index and Coincident load statements
Example:
Leading_Index:
*******,
........
FROM [lib://State Growth Indicators/coincident_leading index monthly tracker.xls]
(biff, embedded labels, table is Leading%_Qlik$);
NOCONCATENATE
Leading:
Load*.......
It worked!
Thank you!