Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
v_jaideep
Creator
Creator

Cross Table in Qlik sense

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 idStudy numberStudy Namescorescoredcountry#AEIssue
1123ABC51USA5421
2456ABC41Canada6723
3789ABC52Poland5534
4143ABC34UK4456
5144ABC35Spain3476

 

Expected Output:

*******

 ABC
Study number 12
  
 123456
countryUSACanada
score54
scored11
issue2123
#AE5467
Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

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

 

Screenshot 2020-09-17 120805.png

 

View solution in original post

7 Replies
Taoufiq_Zarra

can you share the expected output ?

the previous output is not cleare

@v_jaideep 

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

Site idStudy numberStudy Namescorescoredcountry#AEIssue
1123ABC51USA5421
2456ABC41Canada6723
3789ABC52Poland5534
4143ABC34UK4456
5144ABC35Spain3476

@v_jaideep  from this input what is the output ?

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
v_jaideep
Creator
Creator
Author

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
Creator
Creator
Author

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

Kushal_Chawda

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

 

Screenshot 2020-09-17 120805.png

 

v_jaideep
Creator
Creator
Author

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