Load data when there is a new column

[kasiugaa]

Hi again.

I have a problem with loading data. I have qvd files from the last 2 years: File_2021_01_01, File_2021_01_02, ...., File_2022_07_20. I load only 5 columns from each file.

From June this year, I have to load one new, additional column (it did not exist before). Do you know how I can load it?

I can't use: "load * from" because there are 30 columns in each file. It will take a long time to load all columns from the last 2 years.

[hic · Accepted solution]

If the files always have the same form of name, e.g. "File_2021_01_01.qvd", I would do something along the following:

For each vFileName in Filelist ('C:\Path\File_*.qvd')
Let vShortFileName = SubField(vFileName,'\',-1) ;
Let vDate = Date#(TextBetween(vShortFileName,'_','.'),'YYYY_MM_DD') ;
Let vSixthField = If(vDate>=MakeDate(2022,6),'Field6,','') ;
Set vConcatenate = ;

Table:
$(vConcatenate)
Load
$(vSixthField)
'$(vFileName)' as FileName,
'$(vDate)' as FileDate,
Field1,Field2,Field3,Field4,Field5
From [$(vFileName)] (qvd);
Set vConcatenate = Concatenate;
Next vFileName

So, the $(vSixthField) will expand to nothing before June and to 'Field6,' after June, thus loading the extra field.

The dollar expansions will make the syntax checker go crazy, but it is possible to run the script anyway. 

[kasiugaa]

Thank you so much ! It is working 🙂

[kasiugaa]

No sorry. Now I reload whole code and I have a problem. 

Yours solucion creates two tables:
Table and Table-1

In next step, when I am trying to select new column from Table, I have an error that it is not exist. So I tried to do concatinate those two tables, but then I have an error that Table-1 is not exist. 

I am thinking, that maybe I should do two loads: first before June with 'N/A' as NewColumn and the second one for files after June with new column. And then concatenate them. But I don't know how to do that. 

I found the solution. "Set vConcatenate = ;" should be in first line 🙂