Solved: Changing field name with a for loop, names coming ... - Qlik Community

Dilie · ‎2020-09-11

I have 2 tables.

The first one is build up like this:

Table 1:

id | value

customfield1 | rank

customfield2 | email

customfield3 | heigh

And another table build up like this:

Table 2:

id | customfield1 | customfield2 | customfield3

1 | 1 | test@email.com | 189 cm

2 | 1 | test@email.com | 150 cm

3 | 1 | test@email.com | 170 cm

Now I want to change the name of the fields from table 2 with the values from table 1. Is this even possible to do? I tried looping but I didn't succeed.

The tables are only a build up example, they are both coming from an REST connector.

Thanks in advance!

Kushal_Chawda · ‎2020-09-11

@Dilie you don't even need for loop to do this.. see below

Map:
mapping load * inline [
id,value
customfield1,rank
customfield2,email
customfield3,heigh
];

Table:
load * inline [
id,customfield1,customfield2,customfield3
1,1,est@email.com,189 cm
2,1,test@email.com,150 cm
3,1,test@email.com,170 cm

];

RENAME Fields using Map;

View solution in original post

Taoufiq_Zarra · ‎2020-09-11

@Dilie

I found a nice match 😁

Map:
mapping load * inline [
id,value
customfield1,rank
customfield2,email
customfield3,heigh
];

Table:
load * inline [
id,customfield1,customfield2,customfield3
1,1,est@email.com,189 cm
2,1,test@email.com,150 cm
3,1,test@email.com,170 cm

];

for i=1 to NoOfFields('Table')

  let VFieldName=FieldName(i,'Table');
  let mapVFieldName= applymap('Map',FieldName(i,'Table'));
  
 RENAME FIELD '$(VFieldName)' TO '$(mapVFieldName)';

next;

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Dilie · ‎2020-09-11

@Taoufiq_Zarra

Ok so I changed it to my code.

for i=1 to NoOfFields('fields')

  let VFieldName=FieldName(i,'fields');
  let mapVFieldName= applymap('names',FieldName(i,'fields'));
  
 RENAME FIELD '$(VFieldName)' TO '$(mapVFieldName)';

next;

The names table is the one from my previous thread!

This is the fields table:

[fields]:
LOAD	[customfield_10110],
	[customfield_11200],
	[customfield_10111],
	[customfield_11202],
	[customfield_10107],
	[customfield_10900],
	[customfield_10108],
	[customfield_10901],
	[customfield_10902],
	[customfield_10903],
	[lastViewed],
	[customfield_10100],
	[customfield_10101],
	[customfield_12003],
	[customfield_10102],
	[customfield_10103],
	[customfield_10338],
	[timeestimate],
	[aggregatetimeoriginalestimate],
	[customfield_11300],
	[aggregatetimeestimate],
	[customfield_12101],
	[customfield_12100],
	[customfield_10315],
	[customfield_10316],
	[timespent],
	[aggregatetimespent],
	[customfield_10303],
	[customfield_10700],
	[customfield_10701],
	[customfield_10702],
	[resolutiondate],
	[workratio],
	[created],
	[customfield_10300],
	[customfield_11623],
	[customfield_11501],
	[customfield_11622],
	[customfield_11106],
	[customfield_11625],
	[customfield_11624],
	[customfield_11627],
	[customfield_11626],
	[updated],
	[timeoriginalestimate],
	[description_u2],
	[customfield_11101],
	[customfield_11500],
	[customfield_11621],
	[customfield_11620],
	[customfield_11612],
	[customfield_11611],
	[customfield_10800],
	[customfield_11613],
	[customfield_11616],
	[customfield_10801],
	[customfield_11615],
	[customfield_11618],
	[customfield_11617],
	[customfield_11619],
	[summary],
	[customfield_10001],
	[customfield_11610],
	[customfield_10003],
	[customfield_10400],
	[customfield_11601],
	[customfield_11603],
	[customfield_11602],
	[customfield_11605],
	[customfield_11604],
	[customfield_11607],
	[customfield_11606],
	[customfield_11609],
	[customfield_11608],
	[__KEY_fields],
	[__FK_fields] AS [__KEY_issues]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_fields]);

When I run the code I get this error:

The following error occurred:
The name 'description_u2' already exists.
The error occurred here:
RENAME FIELD 'description_u2' TO 'description_u2'

I don't get the error to be honest.

Kushal_Chawda · ‎2020-09-11

@Dilie you don't even need for loop to do this.. see below

Map:
mapping load * inline [
id,value
customfield1,rank
customfield2,email
customfield3,heigh
];

Table:
load * inline [
id,customfield1,customfield2,customfield3
1,1,est@email.com,189 cm
2,1,test@email.com,150 cm
3,1,test@email.com,170 cm

];

RENAME Fields using Map;

Dilie · ‎2020-09-11

@Taoufiq_Zarra

the names for loop:

for i=1 to NoOfFields('names')

  let VFieldName=FieldName(i,'names');



    mapping load distinct

       '$(VFieldName)' as id,

        $(VFieldName) as value

resident names;

next;

Dilie · ‎2020-09-11

@Kushal_Chawda I get this error when I try your code:

The following error occurred:
Field names must be unique within table

The error occurred here:
RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"expand",
		"id" AS "id_u15",
		"self" AS "self_u19",
		"key" AS "key_u4",
		"__KEY_issues",
		"__FK_issues",
		(SELECT 
			"customfield_10110",
			"customfield_11200",
			"customfield_10111",
			"customfield_11202",
			"customfield_10107",
			"customfield_10900",
			"customfield_10108",
			"customfield_10901",
			"customfield_10902",
			"customfield_10903",
			"lastViewed",
			"customfield_10100",
			"customfield_10101",
			"customfield_12003",
			"customfield_10102",
			"customfield_10103",
			"customfield_10338",
			"timeestimate",
			"aggregatetimeoriginalestimate",
			"customfield_11300",
			"aggregatetimeestimate",
			"customfield_12101",
			"customfield_12100",
			"customfield_10315",
			"customfield_10316",
			"timespent",
			"aggregatetimespent",
			"customfield_10303",
			"customfield_10700",
			"customfield_10701",
			"customfield_10702",
			"resolutiondate",
			"workratio",
			"created",
			"customfield_10300",
			"customfield_11623",
			"customfield_11501",
			"customfield_11622",
			"customfield_11106",
			"customfield_11625",
			"customfield_11624",
			"customfield_11627",
			"customfield_11626",
			"updated",
			"timeoriginalestimate",
			"description" AS "description",
			"customfield_11101",
			"customfield_11500",
			"customfield_11621",
			"customfield_11620",
			"customfield_11612",
			"customfield_11611",
			"customfield_10800",
			"customfield_11613",
			"customfield_11616",
			"customfield_10801",
			"customfield_11615",
			"customfield_11618",
			"customfield_11617",
			"customfield_11619",
			"summary",
			"customfield_10001",
			"customfield_11610",
			"customfield_10003",
			"customfield_10400",
			"customfield_11601",
			"customfield_11603",
			"customfield_11602",
			"customfield_11605",
			"customfield_11604",
			"customfield_11607",
			"customfield_11606",
			"customfield_11609",
			"customfield_11608",
			"__KEY_fields",
			"__FK_fields",
			(SELECT 
				"self",
				"id",
				"description",
				"name",
				"__FK_resolution"
			FROM "resolution" FK "__FK_resolution"),
			(SELECT 
				"self" AS "self_u0",
				"value",
				"id" AS "id_u0",
				"__FK_customfield_10500"
			FROM "customfield_10500" FK "__FK_customfield_10500"),
			(SELECT 
				"self" AS "self_u1",
				"value" AS "value_u0",
				"id" AS "id_u1",
				"__FK_customfield_10501"
			FROM "customfield_10501" FK "__FK_customfield_10501"),
			(SELECT 
				"self" AS "self_u2",
				"value" AS "value_u1",
				"id" AS "id_u2",
				"__FK_customfield_12002"
			FROM "customfield_12002" FK "__FK_customfield_12002"),
			(SELECT 
				"@Value",
				"__FK_labels"
			FROM "labels" FK "__FK_labels" ArrayValueAlias "@Value"),
			(SELECT 
				"self" AS "self_u3",
				"value" AS "value_u2",
				"id" AS "id_u3",
				"__FK_customfield_12005"
			FROM "customfield_12005" FK "__FK_customfield_12005"),
			(SELECT 
				"@Value" AS "@Value_u0",
				"__FK_issuelinks"
			FROM "issuelinks" FK "__FK_issuelinks" ArrayValueAlias "@Value_u0"),
			(SELECT 
				"self" AS "self_u4",
				"name" AS "name_u0",
				"key",
				"emailAddress",
				"displayName",
				"active",
				"timeZone",
				"__KEY_assignee",
				"__FK_assignee",
				(SELECT 
					"48x48",
					"24x24",
					"16x16",
					"32x32",
					"__FK_avatarUrls"
				FROM "avatarUrls" FK "__FK_avatarUrls")
			FROM "assignee" PK "__KEY_assignee" FK "__FK_assignee"),
			(SELECT 
				"self" AS "self_u6",
				"description" AS "description_u0",
				"iconUrl",
				"name" AS "name_u2",
				"id" AS "id_u5",
				"__KEY_status",
				"__FK_status",
				(SELECT 
					"self" AS "self_u5",
					"id" AS "id_u4",
					"key" AS "key_u0",
					"colorName",
					"name" AS "name_u1",
					"__FK_statusCategory"
				FROM "statusCategory" FK "__FK_statusCategory")
			FROM "status" PK "__KEY_status" FK "__FK_status"),
			(SELECT 
				"self" AS "self_u7",
				"id" AS "id_u6",
				"name" AS "name_u3",
				"__FK_components"
			FROM "components" FK "__FK_components"),
			(SELECT 
				"self" AS "self_u8",
				"name" AS "name_u4",
				"key" AS "key_u1",
				"emailAddress" AS "emailAddress_u0",
				"displayName" AS "displayName_u0",
				"active" AS "active_u0",
				"timeZone" AS "timeZone_u0",
				"__KEY_creator",
				"__FK_creator",
				(SELECT 
					"48x48" AS "48x48_u0",
					"24x24" AS "24x24_u0",
					"16x16" AS "16x16_u0",
					"32x32" AS "32x32_u0",
					"__FK_avatarUrls_u0"
				FROM "avatarUrls" FK "__FK_avatarUrls_u0")
			FROM "creator" PK "__KEY_creator" FK "__FK_creator"),
			(SELECT 
				"@Value" AS "@Value_u1",
				"__FK_subtasks"
			FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value_u1"),
			(SELECT 
				"self" AS "self_u9",
				"name" AS "name_u5",
				"key" AS "key_u2",
				"emailAddress" AS "emailAddress_u1",
				"displayName" AS "displayName_u1",
				"active" AS "active_u1",
				"timeZone" AS "timeZone_u1",
				"__KEY_reporter",
				"__FK_reporter",
				(SELECT 
					"48x48" AS "48x48_u1",
					"24x24" AS "24x24_u1",
					"16x16" AS "16x16_u1",
					"32x32" AS "32x32_u1",
					"__FK_avatarUrls_u1"
				FROM "avatarUrls" FK "__FK_avatarUrls_u1")
			FROM "reporter" PK "__KEY_reporter" FK "__FK_reporter"),
			(SELECT 
				"progress",
				"total",
				"__FK_aggregateprogress"
			FROM "aggregateprogress" FK "__FK_aggregateprogress"),
			(SELECT 
				"@Value" AS "@Value_u2",
				"__FK_customfield_10200"
			FROM "customfield_10200" FK "__FK_customfield_10200" ArrayValueAlias "@Value_u2"),
			(SELECT 
				"self" AS "self_u10",
				"value" AS "value_u3",
				"id" AS "id_u7",
				"__FK_customfield_11801"
			FROM "customfield_11801" FK "__FK_customfield_11801"),
			(SELECT 
				"self" AS "self_u11",
				"value" AS "value_u4",
				"id" AS "id_u8",
				"__FK_customfield_11800"
			FROM "customfield_11800" FK "__FK_customfield_11800"),
			(SELECT 
				"progress" AS "progress_u0",
				"total" AS "total_u0",
				"__FK_progress"
			FROM "progress" FK "__FK_progress"),
			(SELECT 
				"self" AS "self_u12",
				"id" AS "id_u9",
				"description" AS "description_u1",
				"iconUrl" AS "iconUrl_u0",
				"name" AS "name_u6",
				"subtask",
				"avatarId",
				"__FK_issuetype"
			FROM "issuetype" FK "__FK_issuetype"),
			(SELECT 
				"self" AS "self_u13",
				"id" AS "id_u10",
				"key" AS "key_u3",
				"name" AS "name_u7",
				"projectTypeKey",
				"__KEY_project",
				"__FK_project",
				(SELECT 
					"48x48" AS "48x48_u2",
					"24x24" AS "24x24_u2",
					"16x16" AS "16x16_u2",
					"32x32" AS "32x32_u2",
					"__FK_avatarUrls_u2"
				FROM "avatarUrls" FK "__FK_avatarUrls_u2")
			FROM "project" PK "__KEY_project" FK "__FK_project"),
			(SELECT 
				"self" AS "self_u14",
				"value" AS "value_u5",
				"id" AS "id_u11",
				"__FK_customfield_11400"
			FROM "customfield_11400" FK "__FK_customfield_11400"),
			(SELECT 
				"self" AS "self_u15",
				"watchCount",
				"isWatching",
				"__FK_watches"
			FROM "watches" FK "__FK_watches"),
			(SELECT 
				"self" AS "self_u16",
				"value" AS "value_u6",
				"id" AS "id_u12",
				"__FK_customfield_11502"
			FROM "customfield_11502" FK "__FK_customfield_11502"),
			(SELECT 
				"self" AS "self_u17",
				"value" AS "value_u7",
				"id" AS "id_u13",
				"__FK_customfield_11105"
			FROM "customfield_11105" FK "__FK_customfield_11105"),
			(SELECT 
				"self" AS "self_u18",
				"value" AS "value_u8",
				"id" AS "id_u14",
				"__FK_customfield_11102"
			FROM "customfield_11102" FK "__FK_customfield_11102"),
			(SELECT 
				"@Value" AS "@Value_u3",
				"__FK_customfield_10000"
			FROM "customfield_10000" FK "__FK_customfield_10000" ArrayValueAlias "@Value_u3"),
			(SELECT 
				"@Value" AS "@Value_u4",
				"__FK_customfield_10002"
			FROM "customfield_10002" FK "__FK_customfield_10002" ArrayValueAlias "@Value_u4")
		FROM "fields" PK "__KEY_fields" FK "__FK_fields")
	FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root"

Kushal_Chawda · ‎2020-09-11

@Dilie check the names of field in your mapping table that they are not duplicate or same field is already available in table. Only this reason can cause this error.

Taoufiq_Zarra · ‎2020-09-11

@Dilie do you have a rows with the same value in this section ?

Map:
mapping load * inline [
id,value
customfield1,rank
customfield2,email
customfield3,heigh
];

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

Dilie · ‎2020-09-14

@Taoufiq_Zarra @Kushal_Chawda No, I even put a distinct after the load now. It tells me all fields in the table should be unique... But I can't remember having duplicate fields on this table. Maybe it tries to put it multiple times into the table?

Dilie · ‎2020-09-14

@Kushal_Chawda @Taoufiq_Zarra

Ok, I succeeded in removing the errors. But it looks likes there is happening nothing. When I run my script the fields still have the same name.

//Get custom_field fields and link them with their value.
LIB CONNECT TO 'Name connector ()';

temp:
SQL SELECT 
	"__KEY_root",
	(SELECT 
				
                "customfield_10110",
		"customfield_11200",
		"customfield_10111",
		"resolution",
		"customfield_11202",
		"customfield_10500",
		"customfield_10501",
		"customfield_10107",
		"customfield_10900",
		"customfield_10108",
		"customfield_10901",
		"customfield_10902",
		"customfield_10903",
		"lastViewed",
		"customfield_12002",
		"customfield_10100",
		"customfield_10101",
		"customfield_12003",
		"customfield_10102",
		"labels",
		"customfield_12005",
		"customfield_10103",
		"customfield_10338",
		"timeestimate",
		"aggregatetimeoriginalestimate",
		"issuelinks",
		"assignee",
		"status",
		"components",
		"customfield_11300",
		"aggregatetimeestimate",
		"creator",
		"subtasks",
		"reporter",
		"customfield_12101",
		"customfield_12100",
		"aggregateprogress",
		"customfield_10200",
		"customfield_11801",
		"customfield_10315",
		"customfield_11800",
		"customfield_10316",
		"progress",
		"issuetype",
		"timespent",
		"project",
		"aggregatetimespent",
		"customfield_11400",
		"customfield_10303",
		"customfield_10700",
		"customfield_10701",
		"customfield_10702",
		"resolutiondate",
		"workratio",
		"watches",
		"created",
		"customfield_10300",
		"customfield_11502",
		"customfield_11623",
		"customfield_11105",
		"customfield_11501",
		"customfield_11622",
		"customfield_11106",
		"customfield_11625",
		"customfield_11624",
		"customfield_11627",
		"customfield_11626",
		"updated",
		"timeoriginalestimate",
		"description",
		"customfield_11101",
		"customfield_11102",
		"customfield_11500",
		"customfield_11621",
		"customfield_11620",
		"customfield_11612",
		"customfield_11611",
		"customfield_10800",
		"customfield_11613",
		"customfield_11616",
		"customfield_10801",
		"customfield_11615",
		"customfield_11618",
		"customfield_11617",
		"customfield_11619",
		"summary",
		"customfield_10000",
		"customfield_10001",
		"customfield_10002",
		"customfield_11610",
		"customfield_10003",
		"customfield_10400",
		"customfield_11601",
		"customfield_11603",
		"customfield_11602",
		"customfield_11605",
		"customfield_11604",
		"customfield_11607",
		"customfield_11606",
		"customfield_11609",
		"customfield_11608",
		"__FK_names"
	FROM "names" FK "__FK_names")
FROM JSON (wrap on) "root" PK "__KEY_root";

[names]:
NoConcatenate

 LOAD Distinct	[customfield_10110] AS [customfield_10110],
	[customfield_11200] AS [customfield_11200],
	[customfield_10111] AS [customfield_10111],
	[resolution] AS [resolution],
	[customfield_11202] AS [customfield_11202],
	[customfield_10500],
	[customfield_10501],
	[customfield_10107] AS [customfield_10107],
	[customfield_10900] AS [customfield_10900],
	[customfield_10108] AS [customfield_10108],
	[customfield_10901] AS [customfield_10901],
	[customfield_10902] AS [customfield_10902],
	[customfield_10903] AS [customfield_10903],
	[lastViewed] AS [lastViewed],
	[customfield_12002],
	[customfield_10100] AS [customfield_10100],
	[customfield_10101] AS [customfield_10101],
	[customfield_12003] AS [customfield_12003],
	[customfield_10102] AS [customfield_10102],
	[labels],
	[customfield_12005],
	[customfield_10103] AS [customfield_10103],
	[customfield_10338] AS [customfield_10338],
	[timeestimate] AS [timeestimate],
	[aggregatetimeoriginalestimate] AS [aggregatetimeoriginalestimate],
	[issuelinks],
	[assignee] AS [assignee],
	[status],
	[components],
	[customfield_11300] AS [customfield_11300],
	[aggregatetimeestimate] AS [aggregatetimeestimate],
	[creator],
	[subtasks],
	[reporter],
	[customfield_12101] AS [customfield_12101],
	[customfield_12100] AS [customfield_12100],
	[aggregateprogress],
	[customfield_10200],
	[customfield_11801],
	[customfield_10315] AS [customfield_10315],
	[customfield_11800],
	[customfield_10316] AS [customfield_10316],
	[progress] AS [progress],
	[issuetype],
	[timespent] AS [timespent],
	[project],
	[aggregatetimespent] AS [aggregatetimespent],
	[customfield_11400],
	[customfield_10303] AS [customfield_10303],
	[customfield_10700] AS [customfield_10700],
	[customfield_10701] AS [customfield_10701],
	[customfield_10702] AS [customfield_10702],
	[resolutiondate] AS [resolutiondate],
	[workratio] AS [workratio],
	[watches],
	[created] AS [created],
	[customfield_10300] AS [customfield_10300],
	[customfield_11502],
	[customfield_11623] AS [customfield_11623],
	[customfield_11105],
	[customfield_11501] AS [customfield_11501],
	[customfield_11622] AS [customfield_11622],
	[customfield_11106] AS [customfield_11106],
	[customfield_11625] AS [customfield_11625],
	[customfield_11624] AS [customfield_11624],
	[customfield_11627] AS [customfield_11627],
	[customfield_11626] AS [customfield_11626],
	[updated] AS [updated],
	[timeoriginalestimate] AS [timeoriginalestimate],
	[description] AS [description],
	[customfield_11101] AS [customfield_11101],
	[customfield_11102],
	[customfield_11500] AS [customfield_11500],
	[customfield_11621] AS [customfield_11621],
	[customfield_11620] AS [customfield_11620],
	[customfield_11612] AS [customfield_11612],
	[customfield_11611] AS [customfield_11611],
	[customfield_10800] AS [customfield_10800],
	[customfield_11613] AS [customfield_11613],
	[customfield_11616] AS [customfield_11616],
	[customfield_10801] AS [customfield_10801],
	[customfield_11615] AS [customfield_11615],
	[customfield_11618] AS [customfield_11618],
	[customfield_11617] AS [customfield_11617],
	[customfield_11619] AS [customfield_11619],
	[summary] AS [summary],
	[customfield_10000],
	[customfield_10001] AS [customfield_10001],
	[customfield_10002],
	[customfield_11610] AS [customfield_11610],
	[customfield_10003] AS [customfield_10003],
	[customfield_10400] AS [customfield_10400],
	[customfield_11601] AS [customfield_11601],
	[customfield_11603] AS [customfield_11603],
	[customfield_11602] AS [customfield_11602],
	[customfield_11605] AS [customfield_11605],
	[customfield_11604] AS [customfield_11604],
	[customfield_11607] AS [customfield_11607],
	[customfield_11606] AS [customfield_11606],
	[customfield_11609] AS [customfield_11609],
	[customfield_11608] AS [customfield_11608]
RESIDENT temp
WHERE NOT IsNull([__FK_names]);




for i=1 to NoOfFields('names')

  let VFieldName=FieldName(i,'names');


Map:
     
     mapping load distinct

       '$(VFieldName)' as id,

        $(VFieldName) as value

resident names;


next;

DROP TABLE temp;


//test side
LIB CONNECT TO 'SC ISSUE CONNECTOR ()';

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"expand",
		"id" AS "id_u14",
		"self" AS "self_u17",
		"key" AS "key_u3",
		"__KEY_issues",
		"__FK_issues",
		(SELECT 
			"customfield_10110",
			"customfield_11200",
			"customfield_10111",
			"resolution",
			"customfield_11202",
			"customfield_10107",
			"customfield_10900",
			"customfield_10108",
			"customfield_10901",
			"customfield_10902",
			"customfield_10903",
			"lastViewed",
			"customfield_10100",
			"customfield_10101",
			"customfield_12003",
			"customfield_10102",
			"customfield_10103",
			"customfield_10338",
			"timeestimate",
			"aggregatetimeoriginalestimate",
			"assignee",
			"customfield_11300",
			"aggregatetimeestimate",
			"customfield_12101",
			"customfield_12100",
			"customfield_10315",
			"customfield_10316",
			"timespent",
			"aggregatetimespent",
			"customfield_10303",
			"customfield_10700",
			"customfield_10701",
			"customfield_10702",
			"resolutiondate",
			"workratio",
			"created",
			"customfield_10300",
			"customfield_11623",
			"customfield_11501",
			"customfield_11622",
			"customfield_11106",
			"customfield_11625",
			"customfield_11624",
			"customfield_11627",
			"customfield_11626",
			"updated",
			"timeoriginalestimate",
			"description" AS "description_u1",
			"customfield_11101",
			"customfield_11500",
			"customfield_11621",
			"customfield_11620",
			"customfield_11612",
			"customfield_11611",
			"customfield_10800",
			"customfield_11613",
			"customfield_11616",
			"customfield_10801",
			"customfield_11615",
			"customfield_11618",
			"customfield_11617",
			"customfield_11619",
			"summary",
			"customfield_10001",
			"customfield_11610",
			"customfield_10003",
			"customfield_10400",
			"customfield_11601",
			"customfield_11603",
			"customfield_11602",
			"customfield_11605",
			"customfield_11604",
			"customfield_11607",
			"customfield_11606",
			"customfield_11609",
			"customfield_11608",
			"__KEY_fields",
			"__FK_fields",
			(SELECT 
				"self",
				"value",
				"id",
				"__FK_customfield_10500"
			FROM "customfield_10500" FK "__FK_customfield_10500"),
			(SELECT 
				"self" AS "self_u0",
				"value" AS "value_u0",
				"id" AS "id_u0",
				"__FK_customfield_10501"
			FROM "customfield_10501" FK "__FK_customfield_10501"),
			(SELECT 
				"self" AS "self_u1",
				"value" AS "value_u1",
				"id" AS "id_u1",
				"__FK_customfield_12002"
			FROM "customfield_12002" FK "__FK_customfield_12002"),
			(SELECT 
				"@Value",
				"__FK_labels"
			FROM "labels" FK "__FK_labels" ArrayValueAlias "@Value"),
			(SELECT 
				"self" AS "self_u2",
				"value" AS "value_u2",
				"id" AS "id_u2",
				"__FK_customfield_12005"
			FROM "customfield_12005" FK "__FK_customfield_12005"),
			(SELECT 
				"@Value" AS "@Value_u0",
				"__FK_issuelinks"
			FROM "issuelinks" FK "__FK_issuelinks" ArrayValueAlias "@Value_u0"),
			(SELECT 
				"self" AS "self_u4",
				"description",
				"iconUrl",
				"name" AS "name_u0",
				"id" AS "id_u4",
				"__KEY_status",
				"__FK_status",
				(SELECT 
					"self" AS "self_u3",
					"id" AS "id_u3",
					"key",
					"colorName",
					"name",
					"__FK_statusCategory"
				FROM "statusCategory" FK "__FK_statusCategory")
			FROM "status" PK "__KEY_status" FK "__FK_status"),
			(SELECT 
				"self" AS "self_u5",
				"id" AS "id_u5",
				"name" AS "name_u1",
				"__FK_components"
			FROM "components" FK "__FK_components"),
			(SELECT 
				"self" AS "self_u6",
				"name" AS "name_u2",
				"key" AS "key_u0",
				"emailAddress",
				"displayName",
				"active",
				"timeZone",
				"__KEY_creator",
				"__FK_creator",
				(SELECT 
					"48x48",
					"24x24",
					"16x16",
					"32x32",
					"__FK_avatarUrls"
				FROM "avatarUrls" FK "__FK_avatarUrls")
			FROM "creator" PK "__KEY_creator" FK "__FK_creator"),
			(SELECT 
				"@Value" AS "@Value_u1",
				"__FK_subtasks"
			FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value_u1"),
			(SELECT 
				"self" AS "self_u7",
				"name" AS "name_u3",
				"key" AS "key_u1",
				"emailAddress" AS "emailAddress_u0",
				"displayName" AS "displayName_u0",
				"active" AS "active_u0",
				"timeZone" AS "timeZone_u0",
				"__KEY_reporter",
				"__FK_reporter",
				(SELECT 
					"48x48" AS "48x48_u0",
					"24x24" AS "24x24_u0",
					"16x16" AS "16x16_u0",
					"32x32" AS "32x32_u0",
					"__FK_avatarUrls_u0"
				FROM "avatarUrls" FK "__FK_avatarUrls_u0")
			FROM "reporter" PK "__KEY_reporter" FK "__FK_reporter"),
			(SELECT 
				"progress",
				"total",
				"__FK_aggregateprogress"
			FROM "aggregateprogress" FK "__FK_aggregateprogress"),
			(SELECT 
				"@Value" AS "@Value_u2",
				"__FK_customfield_10200"
			FROM "customfield_10200" FK "__FK_customfield_10200" ArrayValueAlias "@Value_u2"),
			(SELECT 
				"self" AS "self_u8",
				"value" AS "value_u3",
				"id" AS "id_u6",
				"__FK_customfield_11801"
			FROM "customfield_11801" FK "__FK_customfield_11801"),
			(SELECT 
				"self" AS "self_u9",
				"value" AS "value_u4",
				"id" AS "id_u7",
				"__FK_customfield_11800"
			FROM "customfield_11800" FK "__FK_customfield_11800"),
			(SELECT 
				"progress" AS "progress_u0",
				"total" AS "total_u0",
				"__FK_progress"
			FROM "progress" FK "__FK_progress"),
			(SELECT 
				"self" AS "self_u10",
				"id" AS "id_u8",
				"description" AS "description_u0",
				"iconUrl" AS "iconUrl_u0",
				"name" AS "name_u4",
				"subtask",
				"avatarId",
				"__FK_issuetype"
			FROM "issuetype" FK "__FK_issuetype"),
			(SELECT 
				"self" AS "self_u11",
				"id" AS "id_u9",
				"key" AS "key_u2",
				"name" AS "name_u5",
				"projectTypeKey",
				"__KEY_project",
				"__FK_project",
				(SELECT 
					"48x48" AS "48x48_u1",
					"24x24" AS "24x24_u1",
					"16x16" AS "16x16_u1",
					"32x32" AS "32x32_u1",
					"__FK_avatarUrls_u1"
				FROM "avatarUrls" FK "__FK_avatarUrls_u1")
			FROM "project" PK "__KEY_project" FK "__FK_project"),
			(SELECT 
				"self" AS "self_u12",
				"value" AS "value_u5",
				"id" AS "id_u10",
				"__FK_customfield_11400"
			FROM "customfield_11400" FK "__FK_customfield_11400"),
			(SELECT 
				"self" AS "self_u13",
				"watchCount",
				"isWatching",
				"__FK_watches"
			FROM "watches" FK "__FK_watches"),
			(SELECT 
				"self" AS "self_u14",
				"value" AS "value_u6",
				"id" AS "id_u11",
				"__FK_customfield_11502"
			FROM "customfield_11502" FK "__FK_customfield_11502"),
			(SELECT 
				"self" AS "self_u15",
				"value" AS "value_u7",
				"id" AS "id_u12",
				"__FK_customfield_11105"
			FROM "customfield_11105" FK "__FK_customfield_11105"),
			(SELECT 
				"self" AS "self_u16",
				"value" AS "value_u8",
				"id" AS "id_u13",
				"__FK_customfield_11102"
			FROM "customfield_11102" FK "__FK_customfield_11102"),
			(SELECT 
				"@Value" AS "@Value_u3",
				"__FK_customfield_10000"
			FROM "customfield_10000" FK "__FK_customfield_10000" ArrayValueAlias "@Value_u3"),
			(SELECT 
				"@Value" AS "@Value_u4",
				"__FK_customfield_10002"
			FROM "customfield_10002" FK "__FK_customfield_10002" ArrayValueAlias "@Value_u4")
		FROM "fields" PK "__KEY_fields" FK "__FK_fields")
	FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root";

[customfield_10500]:
LOAD	[self],
	[value],
	[id],
	[__FK_customfield_10500] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_10500]);


[customfield_10501]:
LOAD	[self_u0],
	[value_u0],
	[id_u0],
	[__FK_customfield_10501] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_10501]);


[customfield_12002]:
LOAD	[self_u1],
	[value_u1],
	[id_u1],
	[__FK_customfield_12002] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_12002]);


[labels]:
LOAD	[@Value],
	[__FK_labels] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_labels]);


[customfield_12005]:
LOAD	[self_u2],
	[value_u2],
	[id_u2],
	[__FK_customfield_12005] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_12005]);


[issuelinks]:
LOAD	[@Value_u0],
	[__FK_issuelinks] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_issuelinks]);


[statusCategory]:
LOAD	[self_u3],
	[id_u3],
	[key],
	[colorName],
	[name],
	[__FK_statusCategory] AS [__KEY_status]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_statusCategory]);


[status]:
LOAD	[self_u4],
	[description],
	[iconUrl],
	[name_u0],
	[id_u4],
	[__KEY_status],
	[__FK_status] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_status]);


[components]:
LOAD	[self_u5],
	[id_u5],
	[name_u1],
	[__FK_components] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_components]);


[avatarUrls]:
LOAD	[48x48],
	[24x24],
	[16x16],
	[32x32],
	[__FK_avatarUrls] AS [__KEY_creator]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_avatarUrls]);


[creator]:
LOAD	[self_u6],
	[name_u2],
	[key_u0],
	[emailAddress],
	[displayName],
	[active],
	[timeZone],
	[__KEY_creator],
	[__FK_creator] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_creator]);


[subtasks]:
LOAD	[@Value_u1],
	[__FK_subtasks] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_subtasks]);


[avatarUrls_u0]:
LOAD	[48x48_u0],
	[24x24_u0],
	[16x16_u0],
	[32x32_u0],
	[__FK_avatarUrls_u0] AS [__KEY_reporter]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_avatarUrls_u0]);


[reporter]:
LOAD	[self_u7],
	[name_u3],
	[key_u1],
	[emailAddress_u0],
	[displayName_u0],
	[active_u0],
	[timeZone_u0],
	[__KEY_reporter],
	[__FK_reporter] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_reporter]);


[aggregateprogress]:
LOAD	[progress],
	[total],
	[__FK_aggregateprogress] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_aggregateprogress]);


[customfield_10200]:
LOAD	[@Value_u2],
	[__FK_customfield_10200] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_10200]);


[customfield_11801]:
LOAD	[self_u8],
	[value_u3],
	[id_u6],
	[__FK_customfield_11801] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_11801]);


[customfield_11800]:
LOAD	[self_u9],
	[value_u4],
	[id_u7],
	[__FK_customfield_11800] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_11800]);


[progress]:
LOAD	[progress_u0],
	[total_u0],
	[__FK_progress] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_progress]);


[issuetype]:
LOAD	[self_u10],
	[id_u8],
	[description_u0],
	[iconUrl_u0],
	[name_u4],
	[subtask],
	[avatarId],
	[__FK_issuetype] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_issuetype]);


[avatarUrls_u1]:
LOAD	[48x48_u1],
	[24x24_u1],
	[16x16_u1],
	[32x32_u1],
	[__FK_avatarUrls_u1] AS [__KEY_project]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_avatarUrls_u1]);


[project]:
LOAD	[self_u11],
	[id_u9],
	[key_u2],
	[name_u5],
	[projectTypeKey],
	[__KEY_project],
	[__FK_project] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_project]);


[customfield_11400]:
LOAD	[self_u12],
	[value_u5],
	[id_u10],
	[__FK_customfield_11400] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_11400]);


[watches]:
LOAD	[self_u13],
	[watchCount],
	[isWatching],
	[__FK_watches] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_watches]);


[customfield_11502]:
LOAD	[self_u14],
	[value_u6],
	[id_u11],
	[__FK_customfield_11502] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_11502]);


[customfield_11105]:
LOAD	[self_u15],
	[value_u7],
	[id_u12],
	[__FK_customfield_11105] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_11105]);


[customfield_11102]:
LOAD	[self_u16],
	[value_u8],
	[id_u13],
	[__FK_customfield_11102] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_11102]);


[customfield_10000]:
LOAD	[@Value_u3],
	[__FK_customfield_10000] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_10000]);


[customfield_10002]:
LOAD	[@Value_u4],
	[__FK_customfield_10002] AS [__KEY_fields]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_customfield_10002]);


[fields]:
LOAD	[customfield_10110],
	[customfield_11200],
	[customfield_10111],
	[resolution],
	[customfield_11202],
	[customfield_10107],
	[customfield_10900],
	[customfield_10108],
	[customfield_10901],
	[customfield_10902],
	[customfield_10903],
	[lastViewed],
	[customfield_10100],
	[customfield_10101],
	[customfield_12003],
	[customfield_10102],
	[customfield_10103],
	[customfield_10338],
	[timeestimate],
	[aggregatetimeoriginalestimate],
	[assignee],
	[customfield_11300],
	[aggregatetimeestimate],
	[customfield_12101],
	[customfield_12100],
	[customfield_10315],
	[customfield_10316],
	[timespent],
	[aggregatetimespent],
	[customfield_10303],
	[customfield_10700],
	[customfield_10701],
	[customfield_10702],
	[resolutiondate],
	[workratio],
	[created],
	[customfield_10300],
	[customfield_11623],
	[customfield_11501],
	[customfield_11622],
	[customfield_11106],
	[customfield_11625],
	[customfield_11624],
	[customfield_11627],
	[customfield_11626],
	[updated],
	[timeoriginalestimate],
	[description_u1],
	[customfield_11101],
	[customfield_11500],
	[customfield_11621],
	[customfield_11620],
	[customfield_11612],
	[customfield_11611],
	[customfield_10800],
	[customfield_11613],
	[customfield_11616],
	[customfield_10801],
	[customfield_11615],
	[customfield_11618],
	[customfield_11617],
	[customfield_11619],
	[summary],
	[customfield_10001],
	[customfield_11610],
	[customfield_10003],
	[customfield_10400],
	[customfield_11601],
	[customfield_11603],
	[customfield_11602],
	[customfield_11605],
	[customfield_11604],
	[customfield_11607],
	[customfield_11606],
	[customfield_11609],
	[customfield_11608],
	[__KEY_fields],
	[__FK_fields] AS [__KEY_issues]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_fields]);
Rename Fields using Map;


[issues]:
LOAD	[expand],
	[id_u14] AS [id_u14],
	[self_u17] AS [self_u17],
	[key_u3] AS [key_u3],
	[__KEY_issues],
	[__FK_issues] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_issues]);


DROP TABLE RestConnectorMasterTable;








/*
for i=1 to NoOfFields('fields')

  let VFieldName=FieldName(i,'fields');
  let mapVFieldName= applymap('names',FieldName(i,'fields'));
  
 RENAME FIELD '$(VFieldName)' TO '$(mapVFieldName)';

next;
*/



DROP TABLE names;

Changing field name with a for loop, names coming from another table