topic Re: Looping or creating a cross table dynamicly in App Development

Looping or creating a cross table dynamicly

Dilie — Fri, 11 Sep 2020 07:22:02 GMT

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?

Re: Looping or creating a cross table dynamicly

Taoufiq_Zarra — Fri, 11 Sep 2020 07:49:21 GMT

@Dilie

if you have a lot and you want to use loop here's a version :

Table: load * inline [ custom_field1,custom_field2,custom_field3 Rank,Support,Help ]; for i=1 to NoOfFields('Table') let VFieldName=FieldName(i,'Table'); Table_Name: load distinct '$(VFieldName)' as id, $(VFieldName) as value resident Table; next; drop table Table;

output:

Re: Looping or creating a cross table dynamicly

Dilie — Fri, 11 Sep 2020 07:46:41 GMT

@Taoufiq_Zarra

for i=1 to NoOfFields('names') let VFieldName=FieldName(i,'names'); Linked_Names: load distinct '$(VFieldName)' as id, $(VFieldName) as value resident RestConnectorMasterTable; next;

I changed it to this code, I can output all my fields now. The only problem is that all fields get added 2 times, 1 time with value and the other time without value..? I only loop 1 time through the NoOfFields right?

Re: Looping or creating a cross table dynamicly

Taoufiq_Zarra — Fri, 11 Sep 2020 07:51:08 GMT

@Dilie yes I forgot to change 3

can you share the input file in the sample I'm using I can't find any errors !

Re: Looping or creating a cross table dynamicly

Taoufiq_Zarra — Fri, 11 Sep 2020 07:52:11 GMT

for i=1 to NoOfFields('RestConnectorMasterTable') let VFieldName=FieldName(i,'RestConnectorMasterTable'); Linked_Names: load distinct '$(VFieldName)' as id, $(VFieldName) as value resident RestConnectorMasterTable; next;

Re: Looping or creating a cross table dynamicly

Dilie — Fri, 11 Sep 2020 07:57:44 GMT

@Taoufiq_Zarra

I am still getting the same thing, code looks cleaner now, thanks for that!

First it is setting the value, then I get a duplicate row without value. I see nothing wrong with the for loop that makes the data load twice?

Re: Looping or creating a cross table dynamicly

Taoufiq_Zarra — Fri, 11 Sep 2020 08:01:46 GMT

@Dilie can you share a sample ?

Re: Looping or creating a cross table dynamicly

Dilie — Fri, 11 Sep 2020 08:07:58 GMT

@Taoufiq_Zarra

I don't know what you mean with a sample but I'll try to provide you as much information as possible.

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]: 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]); for i=1 to NoOfFields('RestConnectorMasterTable') let VFieldName=FieldName(i,'RestConnectorMasterTable'); Linked_Names: load distinct '$(VFieldName)' as id, $(VFieldName) as value resident RestConnectorMasterTable; next; DROP TABLE RestConnectorMasterTable;

If you need any more info then I would like to know it. Is there any place where I can chat with other Qlik users outside of this forum?

Re: Looping or creating a cross table dynamicly

Dilie — Fri, 11 Sep 2020 08:09:07 GMT

@Taoufiq_Zarra

This was my other forum post with the initial question: https://community.qlik.com/t5/Qlik-Sense-App-Development/Changing-row-column-to-value/m-p/1742780#M56894

Re: Looping or creating a cross table dynamicly

Taoufiq_Zarra — Fri, 11 Sep 2020 08:12:21 GMT

@Dilie can you try this version :

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;

Re: Looping or creating a cross table dynamicly

Dilie — Fri, 11 Sep 2020 08:32:12 GMT

@Taoufiq_Zarra

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?

Re: Looping or creating a cross table dynamicly

Taoufiq_Zarra — Fri, 11 Sep 2020 08:41:05 GMT

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