Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

michael_andrews
New Contributor III

Can't figure out friendly field name mappings.

I have an excel file loaded with columns as such.

  

ExistingFieldNameUserFriendlyFieldName
lnk_crcat_idOther Requirement ID
crcat_count_columnOther Requirement Catalog Measure
crcat_nameOther Requirement Name
crcat_type_labelOther Requirement Label
crcat_valid_monthsOther Requirement Valid Months
crcat_external_idOther Requirement Code
crcat_group_nameOther Requirement Group

And my data is loaded like so

LOAD

    crcat_count_column,

    lnk_crcat_id,

    crcat_name,

    crcat_type_label,

    crcat_valid_months,

    crcat_external_id,

    crcat_group_name

FROM [lib://Qlik Sample Data Files (pocqlik_qpoc1)/cmdemo\cmdemo-etl-v2.2.1-ccat-subset\crcat-20160823-141404.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

What I don't understand precisely is how I write the syntax to rename those columns with the excel file. I've read through these

https://community.qlik.com/blogs/qlikviewdesignblog/2012/09/25/how-to-rename-fields

http://www.learnallbi.com/renaming-fields-in-qlikview-using-mapping-load/

But I'm not quite clear on the exact syntax I need.

1 Solution

Accepted Solutions
MVP
MVP

Re: Can't figure out friendly field name mappings.

To load your excel file, you need to create a folder connection, then you can use the wizard to load the data (it should not be that much different than loading in your other three files).

If you only want to rename fields from your third table, just use the field names of your third table in the Excel files first column.

4 Replies
MVP
MVP

Re: Can't figure out friendly field name mappings.

Just follow Henric's blog post:

FieldNameMap:

Mapping Load OldFieldName, NewFieldName From FieldNames ;

Rename Fields using FieldNameMap;

Hence add to your script something like this at the end:

FieldNameMap:

MAPPING LOAD

ExistingFieldName,UserFriendlyFieldName

FROM ... ; // your Excel file source.

Rename Fields using FieldNameMap;

michael_andrews
New Contributor III

Re: Can't figure out friendly field name mappings.

I probably should have been a tad more specific. We have multiple tables in this load script, I just showed one, and I'm also not clear on how I source my excel file. Is it just the name? Like FROM MyFile.xlsx

But to my first question, we have multiple tables like so

LOAD

    ccat_count_column,

    lnk_ccat_id,

    ccat_name,

    ccat_name_abbr,

    ccat_active_date,

    ccat_expire_date,

    ccat_active_months,

    ccat_group_name

FROM [lib://Qlik Sample Data Files (pocqlik_qpoc1)/cmdemo\cmdemo-etl-v2.2.1-ccat-subset\ccat-20160823-141404.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

LOAD

    cert_count_column,

    lnk_cert_id,

    lnk_cmc_id,

    lnk_ccat_id,

    cert_is_valid,

    cert_active_date,

    cert_inactive_date,

    cert_expire_date,

    cert_last_update_date,

    cert_last_update_time

FROM [lib://Qlik Sample Data Files (pocqlik_qpoc1)/cmdemo\cmdemo-etl-v2.2.1-ccat-subset\cert-20160823-141404.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

LOAD

    crcat_count_column,

    lnk_crcat_id,

    crcat_name,

    crcat_type_label,

    crcat_valid_months,

    crcat_external_id,

    crcat_group_name

FROM [lib://Qlik Sample Data Files (pocqlik_qpoc1)/cmdemo\cmdemo-etl-v2.2.1-ccat-subset\crcat-20160823-141404.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

So how do I specify this mapping for just that third load script?

MVP
MVP

Re: Can't figure out friendly field name mappings.

To load your excel file, you need to create a folder connection, then you can use the wizard to load the data (it should not be that much different than loading in your other three files).

If you only want to rename fields from your third table, just use the field names of your third table in the Excel files first column.

michael_andrews
New Contributor III

Re: Can't figure out friendly field name mappings.

Ok thanks, using the connection thing helped me figure it out. For anyone as a reference, this is what I ended up with.

FieldNameMap:

MAPPING LOAD

  ExistingFieldName, UserFriendlyFieldName

FROM [lib://AttachedFiles/ColumnMappings.xlsx]

(ooxml, embedded labels, table is ColumnMappings);

RENAME FIELDS USING FieldNameMap;

Community Browser