loading and join tables in for next loop

spikenaylor1 — Wed, 25 Nov 2020 16:16:04 GMT

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

DataTbl2

DataTbl3

how can I join these in the above code

many thanks for any help or direction

Re: loading and join tables in for next loop

Vegar — Tue, 05 Mar 2019 14:37:51 GMT

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;

topic loading and join tables in for next loop in QlikView

loading and join tables in for next loop

Re: loading and join tables in for next loop