Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
subject | teacher | contract | time | type |
---|---|---|---|---|
english | john | 21 | 5:50 | a1 |
english | john | 21 | 6:30 | b1 |
english | john | 21 | 8:30 | c1 |
math | kyle | 22 | 13:00 | q20 |
math | kyle | 22 | 14:30 | ace |
math | kyle | 22 | 17:00 | iq |
math | kyle | 22 | 21:00 | ll |
french | peter | 23 | 6:30 | c1 |
french | john | 23 | 7:30 | b1 |
french | peter | 21 | 8:30 | c1 |
french | peter | 23 | 14:30 | s1 |
french | kyle | 24 | 17:30 | q1 |
This data should then effectively be displayed as follows
subject | teacher | contract | |||||
---|---|---|---|---|---|---|---|
english | john | 21 | time type | 5:50 a1 | 6:30 b1 | 8:30 c1 | |
math | kyle | 22 | 13:00 q20 | 14:30 ace | 17:00 iq | 21:00 ll | |
french | peter | 21 | 8:30 c1 | ||||
french | john | 23 | 7:30 b1 | ||||
french | peter | 23 | 6:30 c1 | 14:30 s1 | |||
french | kyle | 24 | 17:30 q1 |
should i now select john with b1 & c1 the display should look as below
subject | teacher | contract | ||
---|---|---|---|---|
english | john | 21 | 6:30 b1 | 8:30 c1 |
french | john | 23 | 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.
can you give an example of how the result should look like with some selections made?
Maybe as a concat like: time & chr(10) & type
- Marcus
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.
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.
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:
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
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.