Solved: Looping or creating a cross table dynamicly - Qlik Community

Dilie · ‎2020-09-11

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?

Taoufiq_Zarra · ‎2020-09-11

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

Regards,
Taoufiq ZARRA

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

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

View solution in original post

Taoufiq_Zarra · ‎2020-09-11

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

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

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?

Taoufiq_Zarra · ‎2020-09-11

@Dilie yes I forgot to change 3

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;

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

Regards,
Taoufiq ZARRA

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

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

Taoufiq_Zarra · ‎2020-09-11

for i=1 to NoOfFields('RestConnectorMasterTable')

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

  Linked_Names:

  load distinct

       '$(VFieldName)' as id,

        $(VFieldName) as value

resident RestConnectorMasterTable;

next;

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

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?

Taoufiq_Zarra · ‎2020-09-11

@Dilie can you share a sample ?

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

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?

Dilie · ‎2020-09-11

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

Taoufiq_Zarra · ‎2020-09-11

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

Regards,
Taoufiq ZARRA

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

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