Does Field Order Matter?

When adding a new field to an existing Qlik LOAD statement, where in the field list should you insert the new field? What if I want to add:

1 as OrderCounter

to this existing statement.

Orders:
LOAD
  OrderId,
  CustomerId,
  OrderDate,
  Sales
FROM lib://mydata/sales.qvd (qvd);

Should I insert at the top of the list?  Bottom, alphabetically or logical grouping?

In most cases it doesn’t matter.  However, if you are changing field names at the same time it may matter.

Let’s walk through an example where field order does matter.

I have a KPI chart in my app that uses the “Sales” field.

What if I change the name of Sales using an “as” clause and reload?

Orders:
LOAD
  OrderId,
  CustomerId, 
  OrderDate,
  Sales as [Total Sales]
FROM lib://mydata/sales.qvd (qvd);

My KPI still works!

Note that the KPI expression been auto-magically updated to use the new “Total Sales” Field name. This automatic fixup is a Qlik feature.

If instead of just a rename I also add a field, would the automagic work?

Orders:
LOAD
  OrderId,
  CustomerId,
  OrderDate,
  1 as OrderCounter,
  Sales as [Total Sales]
FROM lib://mydata/sales.qvd (qvd);

It’s broken! Why didn’t the suto-magic fixup work? The System fields $Table, $Field, $FieldNo provide us a clue. “Sales” is the 4th field in table “Orders”.

Auto-magic fixup depends on the $FieldNo remaining constant. Inserting or deleting a field in the Load statement may change the $FieldNo. That’s when things break.

If I had added the new field at the end, $FieldNo would not change and fixup would have worked.

Here’s your takeaway on  renaming and the auto-magic fixup:

•  Don’t rename and restructure script in the same reload. A limited load of one row is enough to do the fix-up.
•  If in doubt, add new fields to the end of your LOAD statement.

-Rob