Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_klix
Creator II
Creator II

generate a sequence number out of a date field

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

 

1 Solution

Accepted Solutions
rwunderlich
MVP & Luminary
MVP & Luminary

3 Replies
sunny_talwar

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

rwunderlich
MVP & Luminary
MVP & Luminary

michael_klix
Creator II
Creator II
Author

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