Crosstable() and loading files where field headers vary file to file

[stevelord]

Hi, pretty sure I just stepped from my simple solution in a related post to the impossible caveat, but maybe not.

I am trying to load many .xls worksheets from a folder in one load, and have that part down, but the fact they have varying field headers is throwing keeping them from being loaded in script even though I'm trying to get those headers into an Operator name field.

Table is like this:

Client          Joe S     John D     Jane W

Acme          1          3               0

AcmePlus     2                         1

AcmeThree     2          2

I've got crosstable working on it fine to make my Client, Operator, and Data fields, but it won't load files that happen to not have one of the three operators above (say it was their day off and upstream automation isn't producing their column if they have no values for it).

Hm... maybe if I forget the field headers I'll be fine as long as column 1 is client and columns 2 through a zillion are who cares... like loading @1 as the primary thing and @2 onward as the attribute stuff...Like no labels instead of embedded labels...

If someone gets me a correct answer and my midpost thought bombs, you can have correct answer credit.  I'll let you guys know if my midpost through worked out too.

[rwunderlich · Accepted solution]

It's pretty common to do a crosstable load as

LOAD *

then you don't need to worry about how many columns you have.

-Rob

[stevelord]

Yep, my midpost thought bombed even though it was pretty brilliant.  Any advise would be welcome.  Thanks!

(And in the meantime I am asking the source of the datafeed if they can make their output keep all of the operator names even if they have no data, or better yet to put the table into a columner/straight table format since operators might be termed or hired outright.)

[stevelord]

That came close.  It's loading * from the multiple files, but I'm getting synthetic keys and wonky outputs on name/data fields and can't do a concatenate load on a crosstable load.  (I've been using concatenate loads to let other files in the compilation simply add new fields to the table rather than try to synthetic key join them in crazy ways.)

It looks like the field in column one has different header values too which is probably a deal breaker for this * filedump into qlikview load anyway.  If I can get the person to fix that, they can probably re-structure the rest to be a little less zany.

I can't tell if your answer is correct yet given the column one flaw in the data source files, but will come back and upgrade to correct if other person fixing column one lets your answer work for me!  Little mess here, sorry.

[stevelord]

Your answer is correct. I just need my boss to get the vendor to fix inconsistent naming conventions that are messing up the load.