Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

b_garside
Valued Contributor

How to use ApplyMap & RENAME for GUID Field names to readable ones?

I have a Table that's stores questions identified with GUID's instead of the literal questions names I need to translate them using ApplyMap and RENAME

The problem I'm having is how do I translate field names to readable values. I dont know how to map field names to something else.

the manual way to do this would be to alias all the fields....

Mapping_Questions:
Mapping LOAD frmqstnQuestionText,
frmqstnGUID;
SQL SELECT frmqstnQuestionText,
frmqstnName
FROM "master".dbo."QV_QuestionNames";

Form_6248:
LOAD prjrptId,
prjrptProjectId as ProjectID,
prjrptFormId,
prjrptReportID,
prjrptDate,

"Qcabd5f36_9b83_46b8_a510_57921949b40b",
"Q0a3bbcfd_456b_414c_93bc_64a65a3aa880",
"Q02ccee4f_c1d4_46d2_b925_82c744287c04",
"Q61f58659_e530_4188_b3a0_1cd9e00a06d1",
"Qfd25bad3_13cc_42a8_99a6_6a18fcf83c32",
"Q95e48c02_3bf0_4b78_abc4_8b7708a2720"
;
SQL SELECT prjrptId,
prjrptProjectId,
prjrptFormId,
prjrptReportID,
prjrptDate,
  "Qcabd5f36_9b83_46b8_a510_57921949b40b",
  "Q0a3bbcfd_456b_414c_93bc_64a65a3aa880",
  "Q02ccee4f_c1d4_46d2_b925_82c744287c04",
  "Q61f58659_e530_4188_b3a0_1cd9e00a06d1",
"Qfd25bad3_13cc_42a8_99a6_6a18fcf83c32",
  "Q95e48c02_3bf0_4b78_abc4_8b7708a2720"
FROM "master".dbo."vw_6248";

1 Solution

Accepted Solutions

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

If the mapping tables contains the guids and the names to replace them with then add a line at the end of the script:

Mapping_Questions:
Mapping SQL SELECT frmqstnGUID,frmqstnQuestionText
FROM "master".dbo."QV_QuestionNames";

...other stuff

rename fields using Mapping_Questions;


talk is cheap, supply exceeds demand
15 Replies

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

If the mapping tables contains the guids and the names to replace them with then add a line at the end of the script:

Mapping_Questions:
Mapping SQL SELECT frmqstnGUID,frmqstnQuestionText
FROM "master".dbo."QV_QuestionNames";

...other stuff

rename fields using Mapping_Questions;


talk is cheap, supply exceeds demand
rustyfishbones
Honored Contributor II

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

Check this blog post from Qlikview Addict

http://www.qlikviewaddict.com/

b_garside
Valued Contributor

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

For soem reason its not working does this look correct?

Table view

2013-05-10_131108.png

Mapping_Questions:
MAPPING SQL SELECT
frmqstnName, //Dirty
frmqstnQuestionText     //Clean
FROM "master".dbo."QV_QuestionNames";

Form_6248:
LOAD prjrptId,
prjrptProjectId as ProjectID,
prjrptFormId,
prjrptReportID,
prjrptDate,
"Qcabd5f36_9b83_46b8_a510_57921949b40b",
"Q0a3bbcfd_456b_414c_93bc_64a65a3aa880",
"Q02ccee4f_c1d4_46d2_b925_82c744287c04",
"Q61f58659_e530_4188_b3a0_1cd9e00a06d1",
"Qfd25bad3_13cc_42a8_99a6_6a18fcf83c32",
"Q95e48c02_3bf0_4b78_abc4_8b7708a2720";
SQL SELECT prjrptId,
prjrptProjectId,
prjrptFormId,
prjrptReportID,
prjrptDate,
  "Qcabd5f36_9b83_46b8_a510_57921949b40b",
  "Q0a3bbcfd_456b_414c_93bc_64a65a3aa880",
  "Q02ccee4f_c1d4_46d2_b925_82c744287c04",
  "Q61f58659_e530_4188_b3a0_1cd9e00a06d1",
"Qfd25bad3_13cc_42a8_99a6_6a18fcf83c32",
  "Q95e48c02_3bf0_4b78_abc4_8b7708a2720"
FROM "master".dbo."vw_6248";

Rename fields using Mapping_Questions;

b_garside
Valued Contributor

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

Maybe I need to get rid of the Preceding load for the renaming to take effect?

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

In the table view image the guids contain hyphens, not underscore characters. Make sure you use the exact same case sensitive field names.


talk is cheap, supply exceeds demand
b_garside
Valued Contributor

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

Ok, duh, I should have picked up on that. So I will use

a string fucntion Replace(s, fromstring ,tostring)  to fix the _ to - hyphens ?

b_garside
Valued Contributor

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

Thank you so much!

rustyfishbones
Honored Contributor II

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

The Mapping Load Table must be the first sheet in the Script Editor

b_garside
Valued Contributor

Re: How to use ApplyMap & RENAME for GUID Field names to readable ones?

ok, so now I have that part working.

How do I prevent the other non GUID fields from being renamed or destroyed when it doesn't find a match ? 

Community Browser