Loading all fields and changing only one - Best Practice?

[#1]

I come quite often across the issue that I need to load all fields (LOAD * ...) but need to change only one field. How I do it in most cases is to create a temp field then to drop the original field and then to rename the temp field back to the original field name.

Of course I could specify all fields in the load statement, but this makes the code unnecessary long.

Is the any more elegant way?

Very simple example below where I just need to group two values into one.

Thanks, Lukas

LOAD
 *,
 if(REPORTING_SEGMENT='MM' OR REPORTING_SEGMENT='LE','LEMM', REPORTING_SEGMENT) as REPORTING_SEGMENT2

FROM Order_Data.qvd(qvd);
Drop Field REPORTING_SEGMENT;
RENAME Field REPORTING_SEGMENT2 to REPORTING_SEGMENT;

[#2 · Accepted solution]

I generally prefer to have the fields explicitly named as it makes it easier to find things in the script, but thats a minor thing I guess. Using the load wizard to create the statement means there is no overhead with this. Generally though I end up renaming fields from the source so this is the best place to do it and doesnt affect optimisation.

However,I believe that where you have a field that contains numbers and text (which Qlikview regards as 'mixed') if that field is implicitily defined with the '*' then its attribute in Qlikview will be mixed and you will have to fiddle to make it otherwise, so the best place to force it to 'text' is when first referenced. This does affect optimisation though.

I wish Qliktech would publish some definitive technical write ups on optimisation and techniques though. I have spent many happy hours testing various scenarios and often fine the results inconclusive.

Regards,

Gordon

[johnw]

I agree with Gordon on explicitly naming fields, but my opinion is much stronger. I STRONGLY caution against ever using a LOAD * from a QVD in a real application. "Anyone" can come along and add fields to a QVD for thier own purposes. If your application didn't need those fields before, it probably doesn't need them now. So at a minimum the fields are wasting space and time. But it gets worse. Let's say you're loading in several QVDs, and one of them has field "Date" on it. Someone decides that they need a date on one of the other QVDs you're loading, and they call it "Date". Now you have a link in your application that you never intended.

The way to protect yourself is to load ONLY the fields you need. And in the long run, maintaining explicit field lists will likely require less effort than verifying that all of your applications are still working correctly every time you add a field to any QVD.

But if you don't agree with my advice, then yeah, the approach you're using seems fine. I can't think of a more elegant way to handle it.

[#4]

Gordon, John,

thank you very much for your advice, it's much appreciated.

-Lukas