topic Re: Cross Table in Qlik sense in App Development

Cross Table in Qlik sense

v_jaideep — Sat, 16 Nov 2024 01:27:19 GMT

Hello,

I need to create a cross table in Qlik sense, Current table structure is below, and wanted an output as below. Is it possible?

Current Data

*************

Site id	Study number	Study Name	score	scored	country	#AE	Issue
1	123	ABC	5	1	USA	54	21
2	456	ABC	4	1	Canada	67	23
3	789	ABC	5	2	Poland	55	34
4	143	ABC	3	4	UK	44	56
5	144	ABC	3	5	Spain	34	76

Expected Output:

*******

	ABC
Study number	1	2

	123	456
country	USA	Canada
score	5	4
scored	1	1
issue	21	23
#AE	54	67

Re: Cross Table in Qlik sense

Taoufiq_Zarra — Wed, 16 Sep 2020 12:22:32 GMT

can you share the expected output ?

the previous output is not cleare

@v_jaideep

Re: Cross Table in Qlik sense

Taoufiq_Zarra — Wed, 16 Sep 2020 12:34:38 GMT

Site id	Study number	Study Name	score	scored	country	#AE	Issue
1	123	ABC	5	1	USA	54	21
2	456	ABC	4	1	Canada	67	23
3	789	ABC	5	2	Poland	55	34
4	143	ABC	3	4	UK	44	56
5	144	ABC	3	5	Spain	34	76

@v_jaideep from this input what is the output ?

Re: Cross Table in Qlik sense

v_jaideep — Wed, 16 Sep 2020 12:57:38 GMT

@Taoufiq_Zarra

Re: Cross Table in Qlik sense

Kushal_Chawda — Wed, 16 Sep 2020 15:21:19 GMT

@v_jaideep Do you want to change the structure of the data in load script itself? If you do so it will be difficult for you to create the data model in case you want to join table to create another report .

Re: Cross Table in Qlik sense

v_jaideep — Wed, 16 Sep 2020 17:08:44 GMT

I am not looking for any major changes in the script. Will go ahead only if minor changes required in the script.

Re: Cross Table in Qlik sense

Kushal_Chawda — Thu, 17 Sep 2020 11:08:32 GMT

@v_jaideep try below

data: LOAD "Site id", "Study number", "Study Name", score, scored, country, #AE, Issue FROM [lib://Web] (html, utf8, embedded labels, table is @1); // Values in below inline table is 6 as you have 6 dimensions which need to be displayed. Dim: Load * Inline [ Dim 1 2 3 4 5 6];

Create pivot table

// Dimension =Pick(Dim,'Site id','score','scored','country','#AE','Issue') // Column Dimension =[Study Name] &chr(10)&[Study number] // Measure =Pick(Dim, Concat([Site id],','), sum(score), sum(scored), Concat(country,','), sum([#AE]), sum(Issue))

Re: Cross Table in Qlik sense

v_jaideep — Thu, 17 Sep 2020 13:42:41 GMT

It worked exactly how I wanted it, Thank you !!