topic Re: Find missing fields when comparing QVD's for Concatenate in QlikView

Find missing fields when comparing QVD's for Concatenate

ivandrago — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have multiple QVD's that I need to concatenate t o make one fact table, I want to Optimization to work when reloading the Document but at the moment it doesn't work due to the field differences across the QVD's that are being used.

Is there a tool out there or a process to find which fields need to be in the script for each qvd for the reload to be optimized.

At the moment it is very time consuming, as there could be be between 100 and 150 fields per QVD.

Thanks

Re: Find missing fields when comparing QVD's for Concatenate

YoussefBelloum — Mon, 05 Feb 2018 14:55:21 GMT

Hi,

what is the rule to be applied here ?

Starting from the first QVD, the next ones coming should only contains the fields of the first one ? or you want to load all the new fields (which does not exist on the previous one) each time ?

Re: Find missing fields when comparing QVD's for Concatenate

ivandrago — Mon, 05 Feb 2018 15:01:43 GMT

Hi,

There are a combination of new fields from each qvd, at the moment I am having to load the qvd into the script and then getting all the field names for each qvd, copying the fields to excel and then doing mutiple vlookups to see which ones are missing from different comparisons.

Re: Find missing fields when comparing QVD's for Concatenate

YoussefBelloum — Mon, 05 Feb 2018 15:42:38 GMT

you can create a table that do this report for yo on the script.

something like this must already been solved on the community.

Do you want to do these vlookups on the script ?

Re: Find missing fields when comparing QVD's for Concatenate

ivandrago — Mon, 05 Feb 2018 23:04:37 GMT

Hi Youssef,

So the attached spreadsheet "ConcatenateTables.xlsx" has the tables per worksheet that is loaded into the QlikView Document "MissingFieldsForConcatenate.qvw", this Document generates a QVD per table i.e. S1-TableA.qvd

For the Document "AddedFieldsForConcatenate.qvw" the first thing I did was LOAD each S1 QVD and see which fields were originally being used, I then copied these fields per QVD into the Spreadsheet "FindMissingFields.xlsx", I then did a VLOOKUP and see which ones were missing for each QVD and then I amended the Script to add the missing field names for each of the QVD's; I then reloaded the Document which would create new QVD's calling it S2-******.qvd etc...

The last Document "FinalDocumentOptimized.qvw" I have Concatenate each S2 QVD and this will now be Optimized as all the field names exist in each S2 QVD, this is just an example but it is a long process when there are a lot more field names in a QVD?

Can you think of a better way, or do you know if there is a comparison tool out there that can compare QVD's and then provide a script of the missing fields...

I hope this makes sense?

Thanks

Re: Find missing fields when comparing QVD's for Concatenate

shiveshsingh — Tue, 06 Feb 2018 05:35:04 GMT

You can use a document analyzer to check your application and steps required to make it optimized.

Re: Find missing fields when comparing QVD's for Concatenate

jonathandienst — Tue, 06 Feb 2018 05:54:56 GMT

Its not possible to get an optimized load when concatenating tables with different fields.

Re: Find missing fields when comparing QVD's for Concatenate

ivandrago — Tue, 06 Feb 2018 11:27:47 GMT

Hi,

Which sheet from the DocumentAnalyzer would tell me which fields are missing? I have selected the "Fields" sheet but this will only give you the script to say which fields could be dropped.

Thanks

Re: Find missing fields when comparing QVD's for Concatenate

ivandrago — Tue, 06 Feb 2018 11:29:02 GMT

Yes I know that, that is why I am doing the above and trying to get all possible fields then the Optimization will work because the fields are available.

Re: Find missing fields when comparing QVD's for Concatenate

ivandrago — Wed, 07 Feb 2018 21:55:52 GMT

Anyone got any ideas?

Re: Find missing fields when comparing QVD's for Concatenate

swuehl — Wed, 07 Feb 2018 22:27:59 GMT

Maybe like this.

Re: Find missing fields when comparing QVD's for Concatenate

YoussefBelloum — Thu, 08 Feb 2018 08:39:07 GMT

Simple and efficient