Dynamically select fields for load

[Jason_Michaelides]

Hi all,

Can anyone help me with some load script that loads a list of required fields from a spreadsheet and then only load these fields from a QVD that will have many more fields in it?

Cheers,

Jason

[john_duffy]

Hi Jason.

You may be able to accomplish this using the FieldName function.  See the attached application.

Let me know if this helps.

John.

[#3 · Accepted solution]

Try this:

Temp:

LOAD

    concat(FieldsToLoad, '],[') AS FieldsToLoad

FROM myexcel.xlsx

(ooxml, embedded labels, table is Sheet1) ;

LET vFieldList = '[' & peek('FieldsToLoad') & ']';

Data:

LOAD

    $(vFieldList)

FROM myqvd.qvd (qvd);

DROP TABLE Temp;

[Jason_Michaelides]

Hi Matt,

That looks pretty logical! I'll try it when I'm next at my PC and let you know. Looks like it'll work though.

Cheers,

Jason

[Jason_Michaelides]

Thanks John but as far as I can see your solution will still require a change in the script to change which fields from the QVD were to be loaded.

Jason

[Jason_Michaelides]

Matt - works like a charm.  Thanks loads mate. I just added a WHERE [Load?]='Y' clause to the temp table load and now the administrators can define which fields they want in the data model!

Jason

Message was edited by: Jason Michaelides (expanded).

[#7]

Glad it worked!