topic Re: Dynamic field names in QlikView

Dynamic field names

Anonymous — Tue, 08 Jul 2014 09:56:16 GMT

Hi guys,

I have a document i send out to clients. They fill out their employee details and send it back. Ideally i'd like to import these documents into qlikview however some clients decide to use their own headings. I'd like to be able to dynamically import data based on a set of possible field headings. For example - title could be employee title, user title, title description etc.

Is there any way to do this? I've been google and forum searching without much luck. All i have so far is a simple import:

Load title from (ooxml, embedded labels, table is [Employee data]);

Re: Dynamic field names

tresB — Tue, 08 Jul 2014 09:58:53 GMT

You could use '*' to fetch all fields whatever they names they have like:

Load

From <>;

Re: Dynamic field names

Anonymous — Tue, 08 Jul 2014 10:02:37 GMT

Problem with that is that if i create a table that has 'title' in it and the client has sent through 'titlez'; the table wont recognise it. Ideally i need a way to import in such a way that if it says 'titlez' it converts it to 'title'

Re: Dynamic field names

rbecher — Tue, 08 Jul 2014 10:03:42 GMT

Hi Paul,

if the position of the column is the same you could use:

LOAD A as title // A= first column

FROM

(ooxml, no labels, header is 1 lines, table [Employee data]);

Re: Dynamic field names

Anonymous — Tue, 08 Jul 2014 10:05:42 GMT

That is definately a step in the right direction and something I shall note down for future but doesn't work if they switch them round. For example

Title - Forename

Client sends through

Forename - Title.

Re: Dynamic field names

tresB — Tue, 08 Jul 2014 10:06:14 GMT

Yes, qv would not be able to recognize both of them to be same. It is nearly impossible when - even you can't foresee what the users might send the titles like. There has to be an uniformity in terms of column organising or so....

Re: Dynamic field names

rbecher — Tue, 08 Jul 2014 10:07:10 GMT

Or, use a mapping to rename the fields afterwards:

FieldMap:

MAPPING LOAD * INLINE [

F1, F2

titlez, title

employ title, title

user title, title

];

RENAME FIELDS Using FieldMap:

Re: Dynamic field names

ThornOfCrowns — Tue, 08 Jul 2014 10:08:36 GMT

So basically, you need something to automagically determine the data from a file which may or may not have the correct titles and also may or may not be in the correct order?

Re: Dynamic field names

ThornOfCrowns — Tue, 08 Jul 2014 10:09:43 GMT

That would work, apart from wrinkle number two, in that the Clients may not put the column data in the correct ( expected? ) order!

Re: Dynamic field names

Anonymous — Tue, 08 Jul 2014 10:12:19 GMT

I have a list of possible inputs collated from a years worth of submissions so i have a good idea about what the alternatives are they may use. I just need some way for the load script to work on this - load if column heading = 'titlez', 'employee title' as 'title'

Re: Dynamic field names

rbecher — Tue, 08 Jul 2014 10:13:12 GMT

No way...