Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Numbering events by time

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2016-11-03
06:48 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Numbering events by time

Hello,

once again got a really hard nut to crack:

Let's assume we have students. These students have Exam Attempts.

So we have a table like this:

Student | Exams (Attempt) | Date |
---|---|---|

Student1 | Math | 19.10.2015 |

Student1 | Math | 24.10.2015 |

Student1 | Math | 30.10.2015 |

Student2 | Math | 20.10.2015 |

Student3 | Math | 19.10.2015 |

Student3 | Math | 28.10.2015 |

Now what we would like to do is change the Text Math to Attempt1, Attempt2, Attempt3 etc.

So the final table should change like the following:

Student | Exams (Attempt) | Date |
---|---|---|

Student1 | Attempt 1 | 19.10.2015 |

Student1 | Attempt 2 | 24.10.2015 |

Student1 | Attempt 3 | 30.10.2015 |

Student2 | Attempt 1 | 20.10.2015 |

Student3 | Attempt 1 | 19.10.2015 |

Student3 | Attempt 2 | 28.10.2015 |

The dates of the exams are random. So it means that a math test for one student can be on another date for another student.

So at the moment we are pretty clueless how to procede. Maybe you can give us a hint about a good function we could use or in general how to progress.

- Tags:
- qlikview_scripting

532 Views

5 Replies

tresesco

MVP

2016-11-03
06:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try like:

Load

Student,

**'Attempt' & AutoNumber(RowNo(), Student)** as Exam(Attempt),

Date

From <>;

342 Views

Not applicable

2016-11-03
08:05 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks! Works like a charm!

342 Views

Not applicable

2016-11-04
06:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dear Tresesco Biswas,

it seems to work if we only have one kind of Exam(like Math). But if we have multiple it does not work anymore:

Here is our current code:

Events:

Load

Student,

if(Exams='Math','Math' & AutoNumber(RowNo(),Student),

) as Events,

Date

resident Datatable

WHERE Exams='Math';

;

CONCATENATE

Load

Student,

if(Exams='Biology','Biology' & AutoNumber(RowNo(),Student),

) as Events,

Date

resident Datatable

WHERE Exams='Biology';

;

CONCATENATE

Load

Student,

if(Exams='Chemistry','Chemistry' & AutoNumber(RowNo(),Student),

) as Events,

Date

resident Datatable

WHERE Exams='Chemistry';

;

CONCATENATE

Load

Student,

if(Exams='Physics','Physics' & AutoNumber(RowNo(),Student),

) as Events,

Date

resident Datatable

WHERE Exams='Physics';

The problem is - if we have a student and he has only three Math courses then he has

Math1

Math2

Math3

That is correct.

But if he has 3 Math courses and 2 Biology courses he has:

Math1

Biology2

Math3

Math4

Biology5

Correct would be:

Math1

Biology1

Math2

Math3

Biology2

So we dont know exactly what to do - but one idea would be to substract the amount of Math courses for a student from the Atuonumber for Biology... something like this:

Load

Student,

if(Exams='Biology','Biology' & (AutoNumber(RowNo(),Student)-Count(Math???),

) as Events,

Date

resident Datatable

WHERE Exams='Biology';

;

Unfortunately we dont know if this is the right way and we do not know how to formulate that subquery (if this would be the right way to approach the problem).

So any help apreciated!

342 Views

MarcoWedel

MVP

2016-11-05
07:45 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

sample data

Student | Exams (Attempt) | Date |
---|---|---|

Student1 | Math | 19.10.2015 |

Student1 | Math | 24.10.2015 |

Student1 | Biology | 30.10.2015 |

Student2 | Math | 20.10.2015 |

Student3 | Math | 19.10.2015 |

Student3 | Math | 28.10.2015 |

Student1 | Physics | 15.10.2015 |

Student3 | Biology | 19.10.2015 |

Student2 | Physics | 25.10.2015 |

Student1 | Math | 27.10.2015 |

Student3 | Physics | 23.10.2015 |

Student4 | Math | 05.10.2015 |

Student1 | Biology | 09.10.2015 |

Student3 | Physics | 12.10.2015 |

Student4 | Math | 01.10.2015 |

Student1 | Physics | 03.10.2015 |

Student4 | Physics | 07.10.2015 |

Student4 | Math | 26.10.2015 |

Student1 | Biology | 24.10.2015 |

Student4 | Physics | 17.10.2015 |

342 Views

MarcoWedel

MVP

2016-11-05
08:06 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

the trick is to use a combination of student and exam as second Autonumber() parameter (AutoID), because you're counting per student and exam instead of per student only. So one example extending Tresesco's solution might be:

tabExams:

LOAD RecNo() as ID, *

FROM [https://community.qlik.com/thread/238549] (html, codepage is 1252, embedded labels, table is @3);

Left Join (tabExams)

LOAD ID,

[Exams (Attempt)]&AutoNumber(ID,Student&'/'&[Exams (Attempt)]) as Events

Resident tabExams

Order By Date;

hope this helps

regards

Marco

342 Views