Two date fields compared in calculation

[TheresaB_4]

Hi everyone,

I am new to Qlik sense and I and developing a new Report.

I have two date fields. PayDate(Table B) and DateKey, DiscountFactor(Table B),

I need to allocate a value(discountfactor) based on year and month, where the months and year are the same PayDate=DateKey(yyyy-mm)

 

But I am not sure how to do this.

[Oleg_Troyansky · Accepted solution]

Hi Theresa,

For starters, it would be beneficial to have both dates presented in the same date format. The Paydate in table B appears to be good. For the DateKey, I'd suggest creating another Date field (I'm assuming you need the Key for linking tables), and format it in the same way:

DateA = date(date#(DateKey, 'YYYYMMDD'), 'DD/MM/YYYY')

Then, you can calculate the month for both dates, using the function MonthStart:

MonthA = Monthstart(DateA) in table A

MonthB = MonthStart(Paydate) in table B

Then, you can either join the Discount Factor from table B into table A,

left join (TableA)

LOAD

MonthB as MonthA,

[Discount Factor]

resident TableB

;

or use Mapping to do the same - look up Mapping loads and the function ApplyMap()

Would any of this work for you?

[TheresaB_4]

Thank you so much. Now I know where to start. 

Just bought the book too.