Expand single date to full month in load

[anothergreg]

Is there an easy way to take a single date within a month and expand the value to the entire month?  My to-be-added data has the first of each month and an associated monthly rate along with it.  I want to join it with my existing data on a date field that spans the entire month.

A type of example...

Original Data

Sales Date, Salesman, Merchandise Sold, Price

01/03/2016, John, Car, 10000

06/14/2017, Susan, Motorcycle, 12000

08/31/2014, Pam, Truck, 8000

New Data

Month, Commission

1/1/2016, .03

2/1/2016, .03
...

6/1/2017, .04

...

8/1/2017, .05

Since the commission rate is for the entire month I'd like to have .03 get associated with ALL of January 2016, .04 get associated with ALL of June 2017, etc.  Short of doing a loop through all days in a month, is there an easy way or function for Qlikview to expand this?

[vvira1316]

Hi,

Not sure your data model and if you are having a calendar. If you are having calendar then you can use Sales Date to see it is in what month and calculate commission accordingly.

It may not be necessary to find beginning of month

alternatively you can match the month of commission to month of sales date to use right sales to right commission

if above doesn't help then would you be able to share sample data/calculation. It will help to help you.

BR,

Vijay