Calculating Future Dates with value from another Dimension

[dewan_abdullah]

Hello everyone,

I am trying to calculate future dates based on value inside the dimension and after creating the future dates it must contain a specific value comes from another dimension with the same table

here is a pic of what the data looks like and what is expected with explanation:

I tried ValueLoop but didn't work as expected. My main issue is how to copy the installment amount to all of those future dates.

EDIT: The required is to create a new field that has the installment amount for the next future dates. (future date is nothing but current month plus Remaining months) for every account id.

Thank you,

[Taoufiq_Zarra]

Hi Abdullah,

can you give more detail ?
for example Acount number 1, what do you expect?

Cheers,

Taoufiq

[dwforest]

maybe try IntervalMatch to generate a row for every month via a master calendar with the installment amount based on the opendate and loantenure, something like:

[loans]:

select accountId,

opendate,

addmonths(opendate,loantenure) as enddate

from loantable;

load month

Resident [Calendar];

intervalmatch(month)

load opendate, enddate

resident [loans]

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

[dewan_abdullah]

Hi Toufiq,

Account number is just a unique ID. I have no issues with it.

Each Account number has a fixed  installment amount, I'm trying to generate the same installment amounts for future dates for each account. Those future dates come from the RemainingMonths field added to today's MonthYear and each account number has one Value for this field as well. I hope I made things clearer

Best Regards,

[dewan_abdullah]

I didn't try it yet, but I believe this will only generate the dates without having the installment amount for each date. 

Thanks,

[dewan_abdullah]

in other words there must be some sort of loop maybe to copy the installment amount as long as the remaining months is greater than 0 AND loop(oct-2019, +1month) < oct-2019,+RemainingMonths

so for example an account, that has 3 remaining months and installment of 100$ then the new field will have data for the next 3 months,

Dec-2019, Jan-2019, Feb-2019 with 100$ each.

[Taoufiq_Zarra]

Sorry, abdulah.
I still can't understand it.
take an example of the file you sent.
what is the part you have in the data source? and what is the part you want to have after loading?

cheers,

Taoufiq

[dewan_abdullah]

Hi Toufiq,

Please check the attached excel sheet where I explained what is the data source and what is expected.

Thanks for the help,