Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I asked a question before here.
My data is now like this:
custom_field1 | custom_field2 | custom_field3
Rank Support Help
I want it to change to this:
id | value
custom_field1 | Rank
custom_field2 | Support
custom_field3 | Help
Someone gave me a answer to use this:
Crosstable(Entity, Value, 1)
Load '' as dummy, custom_field1, custom_field2, custom_field3
From Source ;
Drop field dummy;
It worked! So I got a lot of custom fields. How loop through my existing resident to add every field with value to my cross table?
Thanks it is working well now! Could you explain me what went wrong? And is there any place where I can chat with other Qlik users outside of this forum?
@Dilie The problem is that you loaded the table twice.( Actually it wasn't you, but qlikview did an automatic concatenation)
so I added Noconcatenate and then at the end I deleted the first table.
for Qlikuser to my knowledge there is no better place to find them than here
///////////////////////////////
LIB CONNECT TO 'Name connector (json connection string)';
//JSON CONNECTOR (TO JIRA)
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"customfield_10110" AS "customfield_10110_u0",
"customfield_11200" AS "customfield_11200_u0",
"customfield_10111" AS "customfield_10111_u0",
"resolution" AS "resolution_u0",
"customfield_11202" AS "customfield_11202_u0",
"customfield_10500",
"customfield_10501",
"customfield_10107" AS "customfield_10107_u0",
"customfield_10900" AS "customfield_10900_u0",
"customfield_10108" AS "customfield_10108_u0",
"customfield_10901" AS "customfield_10901_u0",
"customfield_10902" AS "customfield_10902_u0",
"customfield_10903" AS "customfield_10903_u0",
"lastViewed" AS "lastViewed_u0",
"customfield_12002",
"customfield_10100" AS "customfield_10100_u0",
"customfield_10101" AS "customfield_10101_u0",
"customfield_12003" AS "customfield_12003_u0",
"customfield_10102" AS "customfield_10102_u0",
"labels",
"customfield_12005",
"customfield_10103" AS "customfield_10103_u0",
"customfield_10338" AS "customfield_10338_u0",
"timeestimate" AS "timeestimate_u0",
"aggregatetimeoriginalestimate" AS "aggregatetimeoriginalestimate_u0",
"issuelinks",
"assignee" AS "assignee_u0",
"status",
"components",
"customfield_11300" AS "customfield_11300_u0",
"aggregatetimeestimate" AS "aggregatetimeestimate_u0",
"creator",
"subtasks",
"reporter",
"customfield_12101" AS "customfield_12101_u0",
"customfield_12100" AS "customfield_12100_u0",
"aggregateprogress",
"customfield_10200",
"customfield_11801",
"customfield_10315" AS "customfield_10315_u0",
"customfield_11800",
"customfield_10316" AS "customfield_10316_u0",
"progress" AS "progress_u1",
"issuetype",
"timespent" AS "timespent_u0",
"project",
"aggregatetimespent" AS "aggregatetimespent_u0",
"customfield_11400",
"customfield_10303" AS "customfield_10303_u0",
"customfield_10700" AS "customfield_10700_u0",
"customfield_10701" AS "customfield_10701_u0",
"customfield_10702" AS "customfield_10702_u0",
"resolutiondate" AS "resolutiondate_u0",
"workratio" AS "workratio_u0",
"watches"
FROM "names" FK "__FK_names")
FROM JSON (wrap on) "root" PK "__KEY_root";
[names]:
noconcatenate
LOAD [customfield_10110_u0] AS [customfield_10110_u0],
[customfield_11200_u0] AS [customfield_11200_u0],
[customfield_10111_u0] AS [customfield_10111_u0],
[resolution_u0] AS [resolution_u0],
[customfield_11202_u0] AS [customfield_11202_u0],
[customfield_10500],
[customfield_10501],
[customfield_10107_u0] AS [customfield_10107_u0],
[customfield_10900_u0] AS [customfield_10900_u0],
[customfield_10108_u0] AS [customfield_10108_u0],
[customfield_10901_u0] AS [customfield_10901_u0],
[customfield_10902_u0] AS [customfield_10902_u0],
[customfield_10903_u0] AS [customfield_10903_u0],
[lastViewed_u0] AS [lastViewed_u0],
[customfield_12002],
[customfield_10100_u0] AS [customfield_10100_u0],
[customfield_10101_u0] AS [customfield_10101_u0],
[customfield_12003_u0] AS [customfield_12003_u0],
[customfield_10102_u0] AS [customfield_10102_u0],
[labels],
[customfield_12005],
[customfield_10103_u0] AS [customfield_10103_u0],
[customfield_10338_u0] AS [customfield_10338_u0],
[timeestimate_u0] AS [timeestimate_u0],
[aggregatetimeoriginalestimate_u0] AS [aggregatetimeoriginalestimate_u0],
[issuelinks],
[assignee_u0] AS [assignee_u0],
[status],
[components],
[customfield_11300_u0] AS [customfield_11300_u0],
[aggregatetimeestimate_u0] AS [aggregatetimeestimate_u0],
[creator],
[subtasks],
[reporter],
[customfield_12101_u0] AS [customfield_12101_u0],
[customfield_12100_u0] AS [customfield_12100_u0],
[aggregateprogress],
[customfield_10200],
[customfield_11801],
[customfield_10315_u0] AS [customfield_10315_u0],
[customfield_11800],
[customfield_10316_u0] AS [customfield_10316_u0],
[progress_u1] AS [progress_u1],
[issuetype],
[timespent_u0] AS [timespent_u0],
[project],
[aggregatetimespent_u0] AS [aggregatetimespent_u0],
[customfield_11400],
[customfield_10303_u0] AS [customfield_10303_u0],
[customfield_10700_u0] AS [customfield_10700_u0],
[customfield_10701_u0] AS [customfield_10701_u0],
[customfield_10702_u0] AS [customfield_10702_u0],
[resolutiondate_u0] AS [resolutiondate_u0],
[workratio_u0] AS [workratio_u0],
[watches]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_names]);
drop table RestConnectorMasterTable;
for i=1 to NoOfFields('names')
let VFieldName=FieldName(i,'names');
Linked_Names:
load distinct
'$(VFieldName)' as id,
$(VFieldName) as value
resident names;
next;
DROP TABLE names;