Datamodel for uneven date sequence

[Asit]

In one scenario, we have data in following format where the audits are happening in an uneven interval.

Audit_Table:

CustomerID	AuditDate	Status
12345	9/1/2018	PASS
12345	10/18/2018	PASS
12345	2/28/2019	FAIL
23456	12/1/2018	PASS
8765	9/9/2018	PASS
2345	2/2/2019	FAIL

Sales_Table:

CustomerID	SalesDate	SALE
12345	9/1/2018	100
12345	10/1/2018	123
12345	11/1/2018	234
12345	12/1/2018	123
12345	1/1/2019	111
12345	2/1/2019	100
23456	12/1/2018	12
8765	9/1/2018	3
8765	10/1/2018	44
2345	2/1/2019	1234

Query: On selection of any intermediate month e.g. Oct’18 , we should able to show below result. i.e. list of customers & sales whose audit happened on that month and if no audit happened in that month then the last available audit details but sales of selected month.

e.g. Expected Output:

Month of selection: Oct’18

CustomerID	AuditDate	Status	Sale
12345	10/18/2018	PASS	123
8765	9/9/2018	PASS	44

Note: date is in ‘MM/DD/YYYY’ format.

Please advice the best way to model the data to achieve above result.

Thanks in advance.

[Vegar · Accepted solution]

I suggest you try to define a start and stop time for each audit transaction and thereafter look into the concept of interval matching the sales data.

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/LoadData/matching-interv...

[Asit]

It worked.. thanks!!