generate a sequence number out of a date field

[michael_klix]

This is my problem:

I have a list of project reports with an issue date and a project topic plus as measure their traffic light r/a/g. What I want to generate is a pivot table that lists vertically all topics and horizontally the Status of the report per issue date.

Unfortunately, the topics are not covered in each issue of the report, so typically every topic occurs only everey 5-6 reports and my line per topic has only an entry every few columns.

What I need is some kind of sequence number  to use as the horizontal dimension. The date of the report can be displayed as text in the field maybe. Ideally, the dummy table below would have only 2 columns as all projects have only 2 occurences. Instead of "datum" I would sort by sequence number then.

This is how the raw data looks :

Lines 1-12 are the first appearance of the topic in the reporting, line 13-13 the second for each topic.

Any hint how to do it? Maybe there are even better ways but currently the automatic calculation of a sequence number is the best idea I have.

During load, I could first sort by project and then by date/datum. And then the lines should get a sequential number for each project starting by 1 to x, depending on how often a status was reported. Or is there something I could use out of the box?

Cheers

Michael

[sunny_talwar]

Can you may be share the above screenshot as an Excel file to test it out?

[rwunderlich · Accepted solution]

Try:

autonumber(datum. projekt)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[michael_klix]

Hi Rob,

perfect hint which was finally extended a bit to fit my needs. As the xls-list of reports does not necessarily has the most recent version at the bottom, I sort them first using "order by":

facts_tmp:
LOAD report_date
,  project
,  status
,  project_end_revised
FROM
Data\status.xlsx
(ooxml, embedded labels, table is STATUS);

NoConcatenate
facts:
LOAD report_date
,  project
,  status
,  project_end_revised
,  autonumber(report_date, project)as report_sequence
resident facts_tmp Order by project, report_date;

drop Table facts_tmp;

Thanks a lot

Michael