Split table with value based on other value

[KML]

Hello

I'm working on a script that will allow me to split a value (xxxEUR) based on the date stamp and the numbers of month left in the year - see below.

Customer ID, Account Name, Value, Date

A, aa, 10000, 01.03.2019
B, bb, 700, 01.06.2019

Should it should end up;

Customer ID, Account Name, Value, Date

A, aa, 1000, 01.03.2019
A, aa, 1000, 01.04.2019
A, aa, 1000, 01.05.2019
A, aa, 1000, 01.06.2019
A, aa, 1000, 01.07.2019
A, aa, 1000, 01.08.2019
A, aa, 1000, 01.09.2019
A, aa, 1000, 01.10.2019
A, aa, 1000, 01.11.2019
A, aa, 1000, 01.12.2019
B, bb, 100, 01.06.2019
B, bb, 100, 01.07.2019
B, bb, 100, 01.08.2019
B, bb, 100, 01.09.2019
B, bb, 100, 01.10.2019
B, bb, 100, 01.11.2019
B, bb, 100, 01.12.2019

Data is placed in an Excel sheet.

How do I make this in an efficient way?

Thanks.

[rwunderlich · Accepted solution]

My sample here shows loading the data inline. You can adapt it to load from your excel instead of "resident raw" and skip the raw load altogether. 

SET DateFormat='DD.MM.YYYY';
Raw:
LOAD * INLINE [
Customer ID, Account Name, Value, Date
A, aa, 10000, 01.03.2019
B, bb, 700, 01.06.2019
]
;

Distributed:
NoConcatenate
LOAD
  [Customer ID],
  [Account Name],
  Value / MonthsToDistribute as Value,
  Date(AddMonths(Date, IterNo()-1)) as Date
While IterNo() <= MonthsToDistribute
;
LOAD
  [Customer ID],
  [Account Name],
  Value,
  Date,
  Month(YearEnd(Date)) - Month(Date) + 1 as MonthsToDistribute
Resident Raw
;
DROP TABLE Raw

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[KML]

Thanks, this worked fine for my purpose.