Cross Table in Qlik sense

[v_jaideep]

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

[Taoufiq_Zarra]

can you share the expected output ?

the previous output is not cleare

@v_jaideep 

[Taoufiq_Zarra]

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 ?

[v_jaideep]

@Taoufiq_Zarra 

[Kushal_Chawda]

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

[v_jaideep]

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

[Kushal_Chawda · Accepted solution]

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

[v_jaideep]

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