topic Re: Load data when there is a new column in QlikView

Load data when there is a new column

kasiugaa — Wed, 20 Jul 2022 11:37:57 GMT

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.

Re: Load data when there is a new column

hic — Wed, 20 Jul 2022 15:52:54 GMT

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.

Re: Load data when there is a new column

kasiugaa — Thu, 21 Jul 2022 10:24:04 GMT

Thank you so much ! It is working 🙂

Re: Load data when there is a new column

kasiugaa — Tue, 02 Aug 2022 12:30:03 GMT

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 🙂