Dynamically add the new columns in QVD?

[dmohanty]

Hi All,

I have a requirement like this below:

In a common folder, I have some new and old versions QVD, that are coming from SAME TABLE. The difference is that (for example), the OLD QVD has 2 fields: ID, Sales and the NEW QVD has ID, Name, Sales.

I have the Load script in Data Model as below:

Load

ID,

Name,

Sales

from .... *.QVD (qvd);

This script will fetch the data from all the NEW and OLD qvds from that folder.

My requirement:

Obviuosly the Load Script is failing where the script is fetching data from OLD qvd, as 'Name' is not available. So I want the functionaliy to be like this: It will search for all the fields in the QVD; IF AVAILABLE, then load the data into QV  and IF NOT AVAILABLE, the Dynamically create the Field and load the script. So that no error should come.

Hope I am clear. Could someone please help on this?

[its_anandrjs]

You can try with * key word and the fields that not required drop them like Drop Field FieldName;

Load

*

from .... *.QVD (qvd);

[Gysbert_Wassenaar · Accepted solution]

I think you will have to use a loop to load each qvd file and explicitly concatenate the loads. Something like this should do the trick:

Set vConcatenate = ;
sub ScanFolder(Root)
        for each FileExtension in 'qvd'
            for each FoundFile in filelist( Root & '\*.' & FileExtension)
                FileList:
                $(vConcatenate)
                LOAD *, '$(FoundFile)' as SourceFile
                FROM [$(FoundFile)] (qvd);
                Set vConcatenate = Concatenate;
            next FoundFile
         next FileExtension
         for each SubDirectory in dirlist( Root & '\*' )
             call ScanFolder(SubDirectory)
         next SubDirectory
end sub

Call ScanFolder('C:\QV_APP1\QVD_FILES') ;

The code above is adapted from an example Henric Cronström posted in this discussion: loop through to load all files from a folder and its subfolders?

[jonathandienst]

Hi

First load the sources using LOAD * and collect into a single table. Then resuident load that table and fill in the null Names with the default value. Like this:

Data1:

Load *

from .... *.QVD (qvd);

Data2:

NoConcatenate

Load

     ID,

     If(IsNull(Name), 'Default Name', Name) As Name,

     Sales

Resident Data1;

Drop Table Data1;

HTH

Jonathan

[dmohanty]

Hi Gysbert,

Thanks for this excellent solution. Learnt a new concept as well.

But I have a problem here as well. I can't use a '*' in Load Script as I am doing some field transformations. So, I have to list down all the fields in the Load Script.

Can this be tweaked some how?

[dmohanty]

Thank you Jonathan for this kind help.

But when I am loading the all the data sources into a single table, actually in QV they are becoming 2 set of tables.

1st table (Table) has ID, Sales

2nd table (Table-1) has ID, Name, Sales.

So when I am  doing a resident from Table, 'Name' is not found.

Could you please help?

[jonathandienst]

Use Gysbert's script in place of the first load, then follow with the second part of my post.

[dmohanty]

Hi Gysbert and Jonathan,

Thank you for all these kind help. Really appreciate!