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

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

b_garside — Fri, 10 May 2013 16:43:59 GMT

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";

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

Gysbert_Wassenaar — Fri, 10 May 2013 16:46:40 GMT

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;

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

rustyfishbones — Fri, 10 May 2013 16:46:43 GMT

Check this blog post from Qlikview Addict

http://www.qlikviewaddict.com/

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

b_garside — Fri, 10 May 2013 17:12:27 GMT

For soem reason its not working does this look correct?

Table view

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;

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

b_garside — Fri, 10 May 2013 17:17:09 GMT

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?

Gysbert_Wassenaar — Fri, 10 May 2013 17:21:31 GMT

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

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

b_garside — Fri, 10 May 2013 17:24:34 GMT

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 ?

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

b_garside — Fri, 10 May 2013 17:25:04 GMT

Thank you so much!

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

rustyfishbones — Fri, 10 May 2013 17:47:28 GMT

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

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

b_garside — Fri, 10 May 2013 17:59:18 GMT

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 ?

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

b_garside — Fri, 10 May 2013 18:00:59 GMT

ok, makes sense. done. How do I prevent the other non GUID fields from being renamed or destroyed when it doesn't find a match ? Is there a way to exclude them?

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

b_garside — Fri, 10 May 2013 18:13:32 GMT

I think using will help but the site example doesnt match my situation in this regard does it?

WHERE len(??????? )>0;

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

rustyfishbones — Fri, 10 May 2013 18:47:42 GMT

Maybe you dont need it

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

b_garside — Fri, 10 May 2013 18:49:32 GMT

All is well. Your right. Its seems like some GUIDs did not have a match so it was making it look like it was messing with other fields.

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

rustyfishbones — Fri, 10 May 2013 18:52:09 GMT

Thanks

Please subcribe to my Youtube page for Qlikview Tutorials

https://www.youtube.com/user/RustyFishBones

They can help, sometimes but not all the time

Regards

Alan

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

b_garside — Fri, 10 May 2013 18:54:42 GMT

Will do. Thanks!