Creating cross table dynamicly

Dilie — Thu, 10 Sep 2020 14:42:02 GMT

Hello I am trying to make my cross table script generate dynamicly. I tried it with a foreloop but really could not solve it. I couldn't find any tag to post my code so ill just paste it here.

LIB CONNECT TO 'Name connector ()';

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",
"created" AS "created_u0",
"customfield_10300" AS "customfield_10300_u0",
"customfield_11502",
"customfield_11623" AS "customfield_11623_u0",
"customfield_11105",
"customfield_11501" AS "customfield_11501_u0",
"customfield_11622" AS "customfield_11622_u0",
"customfield_11106" AS "customfield_11106_u0",
"customfield_11625" AS "customfield_11625_u0",
"customfield_11624" AS "customfield_11624_u0",
"customfield_11627" AS "customfield_11627_u0",
"customfield_11626" AS "customfield_11626_u0",
"updated" AS "updated_u0",
"timeoriginalestimate" AS "timeoriginalestimate_u0",
"description" AS "description_u2",
"customfield_11101" AS "customfield_11101_u0",
"customfield_11102",
"customfield_11500" AS "customfield_11500_u0",
"customfield_11621" AS "customfield_11621_u0",
"customfield_11620" AS "customfield_11620_u0",
"customfield_11612" AS "customfield_11612_u0",
"customfield_11611" AS "customfield_11611_u0",
"customfield_10800" AS "customfield_10800_u0",
"customfield_11613" AS "customfield_11613_u0",
"customfield_11616" AS "customfield_11616_u0",
"customfield_10801" AS "customfield_10801_u0",
"customfield_11615" AS "customfield_11615_u0",
"customfield_11618" AS "customfield_11618_u0",
"customfield_11617" AS "customfield_11617_u0",
"customfield_11619" AS "customfield_11619_u0",
"summary" AS "summary_u0",
"customfield_10000",
"customfield_10001" AS "customfield_10001_u0",
"customfield_10002",
"customfield_11610" AS "customfield_11610_u0",
"customfield_10003" AS "customfield_10003_u0",
"customfield_10400" AS "customfield_10400_u0",
"customfield_11601" AS "customfield_11601_u0",
"customfield_11603" AS "customfield_11603_u0",
"customfield_11602" AS "customfield_11602_u0",
"customfield_11605" AS "customfield_11605_u0",
"customfield_11604" AS "customfield_11604_u0",
"customfield_11607" AS "customfield_11607_u0",
"customfield_11606" AS "customfield_11606_u0",
"customfield_11609" AS "customfield_11609_u0",
"customfield_11608" AS "customfield_11608_u0",
"__FK_names"
FROM "names" FK "__FK_names")
FROM JSON (wrap on) "root" PK "__KEY_root";

[names]:
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],
[created_u0] AS [created_u0],
[customfield_10300_u0] AS [customfield_10300_u0],
[customfield_11502],
[customfield_11623_u0] AS [customfield_11623_u0],
[customfield_11105],
[customfield_11501_u0] AS [customfield_11501_u0],
[customfield_11622_u0] AS [customfield_11622_u0],
[customfield_11106_u0] AS [customfield_11106_u0],
[customfield_11625_u0] AS [customfield_11625_u0],
[customfield_11624_u0] AS [customfield_11624_u0],
[customfield_11627_u0] AS [customfield_11627_u0],
[customfield_11626_u0] AS [customfield_11626_u0],
[updated_u0] AS [updated_u0],
[timeoriginalestimate_u0] AS [timeoriginalestimate_u0],
[description_u2] AS [description_u2],
[customfield_11101_u0] AS [customfield_11101_u0],
[customfield_11102],
[customfield_11500_u0] AS [customfield_11500_u0],
[customfield_11621_u0] AS [customfield_11621_u0],
[customfield_11620_u0] AS [customfield_11620_u0],
[customfield_11612_u0] AS [customfield_11612_u0],
[customfield_11611_u0] AS [customfield_11611_u0],
[customfield_10800_u0] AS [customfield_10800_u0],
[customfield_11613_u0] AS [customfield_11613_u0],
[customfield_11616_u0] AS [customfield_11616_u0],
[customfield_10801_u0] AS [customfield_10801_u0],
[customfield_11615_u0] AS [customfield_11615_u0],
[customfield_11618_u0] AS [customfield_11618_u0],
[customfield_11617_u0] AS [customfield_11617_u0],
[customfield_11619_u0] AS [customfield_11619_u0],
[summary_u0] AS [summary_u0],
[customfield_10000],
[customfield_10001_u0] AS [customfield_10001_u0],
[customfield_10002],
[customfield_11610_u0] AS [customfield_11610_u0],
[customfield_10003_u0] AS [customfield_10003_u0],
[customfield_10400_u0] AS [customfield_10400_u0],
[customfield_11601_u0] AS [customfield_11601_u0],
[customfield_11603_u0] AS [customfield_11603_u0],
[customfield_11602_u0] AS [customfield_11602_u0],
[customfield_11605_u0] AS [customfield_11605_u0],
[customfield_11604_u0] AS [customfield_11604_u0],
[customfield_11607_u0] AS [customfield_11607_u0],
[customfield_11606_u0] AS [customfield_11606_u0],
[customfield_11609_u0] AS [customfield_11609_u0],
[customfield_11608_u0] AS [customfield_11608_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_names]);

Crosstable(Entity, Value, 1)

Load '' as dummy, customfield_11608_u0, customfield_11609_u0, customfield_11606_u0

RESIDENT RestConnectorMasterTable;

Drop field dummy;

DROP TABLE RestConnectorMasterTable;

I am trying to dynamicly generate the values from the fields.

Could anyone help with this? And is there something like Qlik chat so I can talk with other people about Qlik?

topic Creating cross table dynamicly in App Development

Creating cross table dynamicly