topic Re: Dynamically add the new columns in QVD? in QlikView

Dynamically add the new columns in QVD?

dmohanty — Fri, 09 May 2014 07:57:27 GMT

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?

Re: Dynamically add the new columns in QVD?

its_anandrjs — Fri, 09 May 2014 08:05:25 GMT

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

Load

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

Re: Dynamically add the new columns in QVD?

Gysbert_Wassenaar — Fri, 09 May 2014 08:07:26 GMT

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?

Re: Dynamically add the new columns in QVD?

jonathandienst — Fri, 09 May 2014 08:52:05 GMT

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

Re: Dynamically add the new columns in QVD?

dmohanty — Fri, 09 May 2014 08:59:47 GMT

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?

Re: Dynamically add the new columns in QVD?

dmohanty — Fri, 09 May 2014 09:15:46 GMT

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?

Re: Dynamically add the new columns in QVD?

jonathandienst — Fri, 09 May 2014 10:28:03 GMT

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

Re: Dynamically add the new columns in QVD?

dmohanty — Sun, 11 May 2014 09:04:41 GMT

Hi Gysbert and Jonathan,

Thank you for all these kind help. Really appreciate!