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: 
Not applicable

Displaying multiple datarows as one column landscape

Hi,

I have a problem where I need to display data that is grouped under one column in a single row.  Yes i tried pivot tables and no this is not the answer or problem.  As per example

subjectteachercontracttimetype
englishjohn215:50a1
englishjohn216:30b1
englishjohn218:30c1
mathkyle2213:00q20
mathkyle2214:30ace
mathkyle2217:00iq
mathkyle2221:00ll
frenchpeter236:30c1
frenchjohn237:30b1
frenchpeter218:30c1
frenchpeter2314:30s1
frenchkyle2417:30q1

This data should then effectively be displayed as follows

subject
teachercontract

englishjohn21

time

type

5:50

a1

6:30

b1

8:30

c1

mathkyle22

13:00

q20

14:30

ace

17:00

iq

21:00

ll

frenchpeter21

8:30

c1

frenchjohn23

7:30

b1

frenchpeter23

6:30

c1

14:30

s1

frenchkyle24

17:30

q1

should i now select john with b1 & c1 the display should look as below

subjectteachercontract
englishjohn21

6:30

b1

8:30

c1

frenchjohn23

7:30

b1

now the above works with a pivot (sort off). Problem is when i do selections on certain data set the time needs to be filled from left to right without skipping columns and displaying the whole set.

Please help as I am stuck.

6 Replies
MarcoWedel

can you give an example of how the result should look like with some selections made?

marcus_sommer

Maybe as a concat like: time & chr(10) & type

- Marcus

Not applicable
Author

Typically there is a listbox with subjects. They are selected and displayed accordingly. Example 16 subjects available with different teachers and contracts. so the combination can be many. Each subject has set times of operation but each teacher has different timeslots.  So depending on the subject and teacher the timeslots will differ. instead of listing them in a column with many rows i want 1 row with the listing landscaped for the timeslots.

Not applicable
Author

my problem is not the concat of info (already doing that). it is with the listing of the many rows in one single row with the timeslots.  the pivot table is reliant on a "key"field it seems and it causes gaps where the data is missing.

MarcoWedel

Hi,

I tried to combine Marcus_Sommer suggestion (time & chr(10) & type) as pivot table expression with the generation of some timeslot sequence number in the script used as fourth pivot dimension. Here's the result:


QlikCommunity_Thread_150098_Pic1.JPG


tabTimeslots:

LOAD *,

    AutoNumber(time, AutoNumberHash128(subject,teacher,contract)) as TimeslotNo

FROM [http://community.qlik.com/thread/150098] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Not applicable
Author

Hi Marco,

Thanks for the advice.  I have tried something similar on the load making an "Index" field and got the same results. My problem comes in when you select data using the fields, the Timeslot still remains 1 to x but the times get shown in the original assigned slot. ie if english 630 falls away, the placemarker shows '-' in 2 and then in 3 the time 8:30.  I need it to be in 2 and so forth.

Any other ideas?  Basically it is building a new index list on the data dynamically selected.

I added some more data to the model to clarify. If you select john and b1 and c1 you will see there is a gap in english, the time starts at timeslot 2 and i need it to be filled at 1 already.