topic Re: New Column in QlikView

New Column

kevbrown — Thu, 28 Apr 2016 12:16:13 GMT

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

Re: New Column

MK_QSL — Thu, 28 Apr 2016 12:17:38 GMT

While Loading use FileName() as FileName..

Re: New Column

kevbrown — Thu, 28 Apr 2016 12:20:53 GMT

The filename isn't the Unit

Re: New Column

sunny_talwar — Thu, 28 Apr 2016 12:21:45 GMT

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

Re: New Column

MK_QSL — Thu, 28 Apr 2016 12:22:42 GMT

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.

Re: New Column

Anonymous — Thu, 28 Apr 2016 12:24:41 GMT

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

Re: New Column

kevbrown — Thu, 28 Apr 2016 12:24:45 GMT

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.

Re: New Column

sunny_talwar — Thu, 28 Apr 2016 12:26:51 GMT

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;

Re: New Column

jonathandienst — Thu, 28 Apr 2016 12:29:32 GMT

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,

...

Re: New Column

kevbrown — Thu, 28 Apr 2016 12:48:10 GMT

That's great, thanks