Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_andrews
Partner - Creator
Partner - Creator

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
swuehl
MVP
MVP

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.

View solution in original post

4 Replies
swuehl
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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?

swuehl
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

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;