New Column

[kevbrown]

I have 4 spreadsheets UnitA, B, C and D. If I load then into QV I don't know which Unit the data is from so would like to create a new column in the script called Unit that will populate the unit for that spreadsheet. How?

Thanks

[MK_QSL]

While Loading use FileName() as FileName..

[kevbrown]

The filename isn't the Unit

[sunny_talwar]

Is the unit information available in the Excel or how else you would know when unit the data is in?

[MK_QSL]

is your units are file specific? I mean Unit A is coming from only one file, Unit B is also coming from 2nd file but not from 1st file...etc.

[Anonymous]

Kevin

Can you not just pass the name of the unit in your load script i.e. load 'Unit A' as Unit for each spreadsheet loaded

Kindest regards

Brian

[kevbrown]

Yes, I pick up 4 spreadsheets from different locations for each of the 4 units. The spreadsheet all have the same name but are for different units.

[sunny_talwar · Accepted solution]

May be something like this:

Table:

LOAD AllYourFields,

          'Unit A' as Unit

FROM SpreadSheet1;

Concatenate(Table)

LOAD AllYourFields

          'Unit B' as Unit

FROM SpreadSheet2;

Concatenate(Table)

LOAD AllYourFields

          'Unit C' as Unit

FROM SpreadSheet3;

Concatenate(Table)

LOAD AllYourFields

          'Unit D' as Unit

FROM SpreadSheet4;

[jonathandienst]

You can get the file path using FileName() and then map the path to the Unit using a mapping table and ApplyMap():

Map_units:

Mapping LOAD * Inline

[

     FilePath, Unit

     <.... path for unit 1 file ...>, Unit1

     <.... path for unit 2 file ...>, Unit2

      ...

];

Data:

LOAD .... <.. main load from spreadsheets ....>

     ApplyMap('Map_units', FileName()) As Unit,

     ...

[kevbrown]

That's great, thanks