Is there a way to use "Rename fields using FieldMap" instead of "load field as fild_alias" to achieve a certain join schema?

Tue, 25 Sep 2012 16:54:08 GMT

The usual way would be LOAD id AS clientID FROM client.qvd ... and LOAD id AS ClaimID, clientID FROM claim.qvd. So table_name.id should become table_nameID and it will be automatically joined to the foreign key.

But I have 200 such tables and would like to do that all in a loop (i.e. not editing the load statements by hand). Below you find an example of such a loop.

datadictionary: LOAD * Inline [

oldnames, newnames

Client.id,clientID

Claim.id,claimID

Claim.clientID,clientID

];

FieldMap: Mapping load oldnames, newnames Resident datadictionary;

QUALIFY *;

UNQUALIFY clientID;

for each file in filelist('*.qvd')

temp: load FileBasename() as tablename From $(file) (qvd) where RecNo()=1;

let tablename = peek('temp.tablename',0,temp);

'$(tablename)': LOAD * FROM $(file) (qvd);

Rename fields using FieldMap;

DROP table temp;

The problem is "id" from Client table can not be renamed to clientID. I understand this is because it does not happen during the load.

Is there a cure? Can I ask Qlikview to reload that table somehow with the new names?

Second complication is that there are other technical columns (like creation_date, row_status, etc.) in each table. They would cause unwanted synth keys. So I need to use Qualify if I do not want to chase them through all tables. Not sure if this all can live in one script.

I understand this is a bit of a complicated set-up but I am happy to clarify. So please do not hesitate to ask for the clarification.

topic Is there a way to use "Rename fields using FieldMap" instead of "load field as fild_alias" to achieve a certain join schema? in QlikView

Is there a way to use "Rename fields using FieldMap" instead of "load field as fild_alias" to achieve a certain join schema?