attach is a start
it fills all the blanks
and saves only lowest hirarchey row
in this row you have all the information in one row
now all you need is rename the fields
Example.qvw 201.2 K
Thanks very much for your reply. It's certainly a start, and much better than I was managing (so thanks!), but it still doesn't quite read in the data right.
I've attached your document with the columns renamed (for clarity) and I've added a pivot table (as this best reflects how the data was in the source file).
Take Fresnillo PLC, for example. Once I've pivoted the date field to go horizontally along the top, there should only be one row for Fresnillo (as this stock name is the lowest heirarchy) - with multiple columns for "ending weight", "market cap", "# of securities" and "date". However, as the attached document shows, Fresillo appears in 6 rows:
- North America / Mexico / Materials / Metals & Mining (this is the correct classification)
- Western Europe / Mexico / Materials / Metals & Mining
- Western Europe / Spain / Industrials / Airlines
- Western Europe / Spain / Industrials / Metals & Mining
- Western Europe / Spain / Materials / Metals & Mining
It looks like it's reading in some rows below/above the correct row for Fresnillo (and indeed for other stocks).
As you say, it only needs to read from the rows where Stock_Name<>' '. It then needs to pick up the other field values from the first non-empty cells moving left and up.
Thanks for your help so far - would be great if you could suggest a solution to the above problem.
QV read from FactSet output.qvw 233.8 K