topic Optimization of DataModel in QlikView

Optimization of DataModel

datanibbler — Thu, 26 Apr 2018 07:31:12 GMT

Hi,

I've been trying to optimize the DataModel of one app, throwing out of the LOADs a lot of fields which were beforehand deleted at the end (not needed), getting rid of a few subtables which existed only of a key and one more field and such.

One of the tasks involved that took a lot of time was this:

There were (and are still, OMG) quite a few LOADs with the * which load all the fields there are in a (SAP) table - many of these, however, were never used and thus there is a DROP FIELD statement at the end of the datamodel, dropping many of those - that was obviously generated using the DocumentAnalyzer.

Is there some tool that would allow me to quickly scan that DROP FIELD statement, or parts of it, and then generate a LOAD from the corresponding table not taking into account those fields? I realize that involves a few steps, so it's a bit more complex, but it seems worth having to me as I have come across the * quite a few times and I heartily dislike it ...

It is also recommended for loading a number of files in one go, but I had nothing but trouble with that application, too, because then the log_file won't tell you which file the error was in if there is one ..

Maybe it seemed worth making to anyone and someone has uploaded something to that end?

Thanks a lot!

Best regards,

DataNibbler

Re: Optimization of DataModel

marcus_sommer — Thu, 26 Apr 2018 08:01:27 GMT

In general you could load just the first record of such a table to get all included fields. Then running through those fields within a loop and comparing them with your drop-listing and excluding them. The remaining fields could be concatenated within a variable which is then used as field-list within the real load.

I think you would need quite a lot of these statements to get a benefit from such a routine and by just a few of them it would be faster to copy & paste this stuff, applying the "text-in-columns" and the transpose feature and some lookup between them and then some sorting and filtering - all in Excel. At least someone whos is a bit experienced in Excel and knows to use the keyboard could do it in a very short time ...

The last point which I understand as loading from files with a wildcard in the filename - it's often easier to use a for each filelist() loop at least if it comes to failures or other problems like different datastructures or any conditions needs to be applied ...

- Marcus

Re: Optimization of DataModel

datanibbler — Thu, 26 Apr 2018 08:08:21 GMT

Hi Marcus,

you're right - I often did this myself (replacing the * with a filelist-loop).

Such a routine would probably not bring me much of a saving, I guess you're right there, too.

I know my way around Excel reasonably well, so I could do it that way - last time I did this, however, I just generated a full listing of all fields using the hammer-symbol, added the generated fields again from a notepad and then went through the list field by field, searching the script for the fieldname and wherever I found it only in the DROP-statement, I just commented it out here and there. It might well be possible to do this faster in Excel, I will experiment a bit with that. Maybe it would even be possible to do it in Excel using a macro, that would make it even faster - and writing macros is fun, too, so I will give it a go whenever I have the time.

Best regards,

DataNibbler