Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How can I save the variable names of a list of QVD's, and then use left join (with null values), the objective is to have all the QVD's with the same variables.

Hi,

Now I'm working in a loop, extracting data of old periods and creating a resume of the information of clients. But this information it's not always available so I use Set Error to avoid stop the script, with this Set Error the variables doesn't exist, so when I load the different QVD's of different periods, in some cases Qlikview doesn't open the period in the same table (when some variable is missing) so I want to storage all the variable names of all the periods to then, use left join with null values so if the variable exists nothing happens and if it's not, it will be a empty variable, but it will read all the periods in the same table, please help.

Thanks.

4 Replies
hector_munoz_in
Valued Contributor

Re: How can I save the variable names of a list of QVD's, and then use left join (with null values), the objective is to have all the QVD's with the same variables.

Hi Fernando,

What I understand of your question is that you want to load into a resident table the information coming from several historical QVDs with different structure; they donot have the same fields, right? If I am right look at CONCATENATE sentence (previous to LOAD sentence) in order to join into the same table information from different, but similar, sources.

Hope it serve!

Regards,

H

Re: How can I save the variable names of a list of QVD's, and then use left join (with null values), the objective is to have all the QVD's with the same variables.

I'm not sure if I understand your problem right but I think you need another approach by avoiding the set error bypass and loading the qvd's correctly from the beginning. I assume that you mean with "variable names" the included fieldnames of the qvd's and not the filenames of them and that your problem is that not all qvd's contain the same fields but you want to load them per wildcard-load without specifying special load-statements for each of them.

There are several ways possible to handle these situation. For example, are there quite a lot of files but only a few different data-structures you could use a single subfolder for each different type of them and using than a few specialized load-statements. Another method would be to concatenate all qvd's to a dummy-table:

YourTable:

load 'Dummy' as Dummy autogenerate 0;

concatenate (YourTable)

load * from path\qvd-files*.qvd (qvd);

drop fields Dummy;

But of course you could also read all fieldnames from the files and create a load-statement from this on the fly. By qvd's it's quite easy because you could use a loop per QvdNoOfFields() over all fields and per QvdFieldName() you will get the fieldnames.

- Marcus

Not applicable

Re: How can I save the variable names of a list of QVD's, and then use left join (with null values), the objective is to have all the QVD's with the same variables.

Thanks!

Concatenate the tables work perfectly, Marcus what do you suggest to avoid the set error?

Re: How can I save the variable names of a list of QVD's, and then use left join (with null values), the objective is to have all the QVD's with the same variables.

I mean that the use of the error-mode should be always the last measure if an error ocurred and there is really no way to implement the requirred logic without such a bypass to ignore the errors. Ok. that was generally spoken - in the real world it might be more practically respectively more pragmatic to choose those way which is easier to handle either avoiding the errors or correcting them afterwards.

In your case it would be more expensive to correct these errors if a quite simple forced concatenating would avoid it in beforehand.

- Marcus