Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Asit
Contributor
Contributor

Datamodel for uneven date sequence

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.

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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...

View solution in original post

2 Replies
Vegar
MVP
MVP

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
Contributor
Contributor
Author

It worked.. thanks!!