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

Matching multiple fields into time interval

Hello,

Our lab has several procedures that we follow before billing a test. I need to report on the volume flowing through each step weekly.

The data model is simple (although the dates below may be datetimes):

load * inline [

CaseID,kit_received, test_reported, data_entry_start, data_entry_end, ptp_start, ptp_end, auth_start, auth_end, billed_date, claim_submit_date

1,'01-01-2018', '01-15-2018', '01-01-2018', '01-02-2018', '01-02-2018', '01-06-2018', '01-06-2018', '01-11-2018', '01-15-2018', '01-15-2018'

2,'01-02-2018', '01-15-2018', '01-02-2018', '01-02-2018', '01-02-2018', '01-09-2018', '01-10-2018', '01-11-2018', '01-15-2018', '01-15-2018'

3,'01-02-2018', '01-09-2018', '01-03-2018', '01-03-2018', '01-04-2018', '01-06-2018', '01-06-2018', '01-13-2018', '01-14-2018', '01-15-2018'

4,'01-03-2018', '01-09-2018', '01-03-2018', '01-04-2018', '01-05-2018', '01-08-2018', '01-10-2018', '01-12-2018', '01-12-2018', '01-12-2018'

]


What I would want to show is a table like this:

FEF Chart.PNG

or this:

FEF Weeks.PNG

However, I don't know how to create an independent time dimension and to match / interval match different fields to it. Any advice would be appreciated.

2 Replies
eduardo_dimperio
Specialist II
Specialist II

Hi, Maybe using a crosstable:

BASE:

load * inline [

CaseID,kit_received, test_reported, data_entry_start, data_entry_end, ptp_start, ptp_end, auth_start, auth_end, billed_date, claim_submit_date

1,'01-01-2018', '01-15-2018', '01-01-2018', '01-02-2018', '01-02-2018', '01-06-2018', '01-06-2018', '01-11-2018', '01-15-2018', '01-15-2018'

2,'01-02-2018', '01-15-2018', '01-02-2018', '01-02-2018', '01-02-2018', '01-09-2018', '01-10-2018', '01-11-2018', '01-15-2018', '01-15-2018'

3,'01-02-2018', '01-09-2018', '01-03-2018', '01-03-2018', '01-04-2018', '01-06-2018', '01-06-2018', '01-13-2018', '01-14-2018', '01-15-2018'

4,'01-03-2018', '01-09-2018', '01-03-2018', '01-04-2018', '01-05-2018', '01-08-2018', '01-10-2018', '01-12-2018', '01-12-2018', '01-12-2018'

];

Crosstable (FIELDS, DATE) TEST: LOAD * Resident BASE;

DROP TABLE BASE;

MarcoWedel

Hi,

maybe helpful:

Canonical Date

regards

Marco