Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeJones
Creator
Creator

Table output

Hi , the first columns of the table shows some of my raw data available.  To the right shows my desired output. I have tried using Concat and Chr(10) in a pivot table & Table, though this has not worked.  Any ideas would be very welcome.

Engineer Booking Reference Booking Status Time Slot            
Mike Jones A1 In Progress 1pm - 5pm   Mike Jones A3
Aborted
8 am - 12 pm
A2
Completed
8 am - 12 pm
A1
In Progress
1pm - 5pm
 
Mike Jones A3 Aborted 8 am - 12 pm   Adam Smith A5
Completed
8am - 12pm
A4
Completed
8am - 12pm
A10
Booked
1pm - 5pm
A6
Booked
All Day
Mike Jones A2 Completed 8 am - 12 pm   Andrew Hill A8
Completed
8am - 12pm
A9
In Progress
1pm - 5pm
A7
Booked
1pm - 5pm
A11
Booked
1pm - 5pm
Adam Smith A5 Completed 8 am - 12 pm            
Adam Smith A4 Completed 1pm - 5pm            
Adam Smith A6 Booked All Day            
Adam Smith A10 Booked 1pm - 5pm            
Andrew Hill A7 Booked All Day            
Andrew Hill A8 Completed 8 am - 12 pm            
Andrew Hill A11 Booked 1pm - 5pm            
Andrew Hill A9 In Progress 1pm - 5pm            
Labels (4)
1 Solution

Accepted Solutions
KGalloway
Creator II
Creator II

Would something like the following work? I used this code in the load script:

original_table:
load *, AutoNumber(recno(), Engineer) as newColumn1 inline [
Engineer, Booking Reference, Booking Status, Time Slot  
Mike Jones,A1,In Progress,1pm - 5pm
Mike Jones, A3, Aborted, 8 am - 12 pm
Mike Jones, A2, Completed, 8 am - 12 pm
Adam Smith, A5, Completed, 8 am - 12 pm  
Adam Smith, A4, Completed, 1pm - 5pm  
Adam Smith, A6, Booked, All Day  
Adam Smith, A10, Booked, 1pm - 5pm  
Andrew Hill, A7, Booked, All Day  
Andrew Hill, A8, Completed, 8 am - 12 pm  
Andrew Hill, A11, Booked, 1pm - 5pm  
Andrew Hill, A9, In Progress, 1pm - 5pm
]
;
 
NoConcatenate
new_table:
load
Engineer,
    newColumn1,
    [Booking Reference] & chr(10) & [Booking Status] & chr(10) & [Time Slot] as newColumn2
resident original_table;
 
KGalloway_0-1696277452485.png

 

The chr(10) is present in the data (as shown in the bottom table), but the pivot table in the middle seems to ignore it.

Let me know if I can clarify anything.

View solution in original post

2 Replies
KGalloway
Creator II
Creator II

Would something like the following work? I used this code in the load script:

original_table:
load *, AutoNumber(recno(), Engineer) as newColumn1 inline [
Engineer, Booking Reference, Booking Status, Time Slot  
Mike Jones,A1,In Progress,1pm - 5pm
Mike Jones, A3, Aborted, 8 am - 12 pm
Mike Jones, A2, Completed, 8 am - 12 pm
Adam Smith, A5, Completed, 8 am - 12 pm  
Adam Smith, A4, Completed, 1pm - 5pm  
Adam Smith, A6, Booked, All Day  
Adam Smith, A10, Booked, 1pm - 5pm  
Andrew Hill, A7, Booked, All Day  
Andrew Hill, A8, Completed, 8 am - 12 pm  
Andrew Hill, A11, Booked, 1pm - 5pm  
Andrew Hill, A9, In Progress, 1pm - 5pm
]
;
 
NoConcatenate
new_table:
load
Engineer,
    newColumn1,
    [Booking Reference] & chr(10) & [Booking Status] & chr(10) & [Time Slot] as newColumn2
resident original_table;
 
KGalloway_0-1696277452485.png

 

The chr(10) is present in the data (as shown in the bottom table), but the pivot table in the middle seems to ignore it.

Let me know if I can clarify anything.

MikeJones
Creator
Creator
Author

Hi, KGalloway, you are an absolute magician and thanks for your help.