Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
YearSelection: LOAD * From $(vYEARS_Path)$(vYEARS_File) (ooxml, embedded labels, table is Years) Where (Not IsNUll(LoadDatafromFile)); let vMin = peek('Year',0,'YearSelection'); Let vMax = peek('Year',NoOfRows('YearSelection')-1,'YearSelection'); Drop Table YearSelection; For vI = $(vMin) to $(vMax) Let vDataTransform = '$(vPathQVDExtract)' & '000_Data_' & '$(vI)' & '_Extract_MainData.qvd'; // Set the QVD Transform filepath // Load all the transform files and concentate IPC Data DataTbl: Let vData = '$(vPathQVDDashboard)' & '000_Dashboard_Data.qvd'; // Set the QVD Dashboard filepath Load * From $(vDataTransform) (QVD); Next vI Store DataTbl into [$(vData)] (qvd); Drop Table DataTbl;
I am trying to load spreadsheet data for yearly spreadsheets 2015 to 2019, 5 separate spreadsheets.
If all the spreadsheet fields are the same, the data is loaded and auto Concatenated and outputted into one QVD file.
my files must have differing fields throughout the years.
I end up on the load window with
DataTbl
DataTbl1
DataTbl1
DataTbl2
DataTbl3
how can I join these in the above code
many thanks for any help or direction
Try to force a concatenate into your load. This should eliminate your problems.
YearSelection: LOAD * From $(vYEARS_Path)$(vYEARS_File) (ooxml, embedded labels, table is Years) Where (Not IsNUll(LoadDatafromFile)); let vMin = peek('Year',0,'YearSelection'); Let vMax = peek('Year',NoOfRows('YearSelection')-1,'YearSelection');
DataTbl:
LOAD * inline [
DummyField];
Drop Table YearSelection; For vI = $(vMin) to $(vMax) Let vDataTransform = '$(vPathQVDExtract)' & '000_Data_' & '$(vI)' & '_Extract_MainData.qvd'; // Set the QVD Transform filepath // Load all the transform files and concentate IPC Data Let vData = '$(vPathQVDDashboard)' & '000_Dashboard_Data.qvd'; // Set the QVD Dashboard filepath Concatenate(DataTbl)
Load * From $(vDataTransform) (QVD); Next vI Drop field DummyField; Store DataTbl into [$(vData)] (qvd); Drop Table DataTbl;