topic Re: Changing field name with a for loop, names coming from another table in App Development

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

Dilie — Fri, 11 Sep 2020 12:37:30 GMT

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!

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

Taoufiq_Zarra — Fri, 11 Sep 2020 13:26:05 GMT

@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:

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

Dilie — Fri, 11 Sep 2020 13:43:32 GMT

@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.

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

Kushal_Chawda — Fri, 11 Sep 2020 13:46:56 GMT

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

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

Dilie — Fri, 11 Sep 2020 13:47:52 GMT

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

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

Dilie — Fri, 11 Sep 2020 13:51:17 GMT

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

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

Kushal_Chawda — Fri, 11 Sep 2020 13:53:50 GMT

@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.

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

Taoufiq_Zarra — Fri, 11 Sep 2020 13:57:46 GMT

@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
];

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

Dilie — Mon, 14 Sep 2020 07:18:54 GMT

@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?

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

Dilie — Mon, 14 Sep 2020 07:45:13 GMT

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

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

Taoufiq_Zarra — Mon, 14 Sep 2020 08:12:43 GMT

@Dilie I can't decrypt your code very well but try this :

//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]); DROP TABLE temp; 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 names; //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]: NoConcatenate 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; RENAME Fields using Map;