How can I autogenerate rows from year total?

[#1]

Hi Community

I am pretty new to Qlikview and need some help.

I have a table, containing totals per year :

Year	Agent	Customer	Value per Year
2016	Paul	IBD	2000
2016	Paul	ABC	35435
2016	Peter	sadasds	23421
....

And I have a table containing a factor per month:

YearMonth	Factor
2016.01	0.05
2016.02	0.03
...	...
2016.12	0.04

Now I want to calculate the proportian per month depending on the factor. to generate a new table with:

YearMonth	Agent	Customer	Value per Month
2016.01	Paul	IBD	100 (<---- this is "value per year" * "factor")
2016.02	Paul	IBD	60
2016.03	...	...	....
2016.01	Peter	sadasds	1171.05
......

How can I generate this?

Thank you very much for your help.

[sunny_talwar]

I guess something like this:

Table2:

LOAD YearMonth,

          Year(YearMonth) as Year,

          Factor

From 2ndTable;

Left Join (Table2)

LOAD Year,

          [Value per Year]

FROM 1stTable;

FinalTable2:

LOAD *,

          Factor * [Value per Year] as [Value per Month]

Resident Table2;

DROP Table Table2;

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

mapFactor:
Mapping
LOAD Date#(YearMonth,'YYYY.MM'), Factor
Inline [
YearMonth, Factor
2016.01, 0.05
2016.02, 0.03
2016.03, 0.06
2016.04, 0.08
2016.05, 0.10
2016.06, 0.12
2016.07, 0.16
2016.08, 0.12
2016.09, 0.10
2016.10, 0.08
2016.11, 0.06
2016.12, 0.04
];

tabValue:
LOAD *, [Value per Year]*ApplyMap('mapFactor',YearMonth) as [Value per Month];
LOAD *, Date(MakeDate(Year,IterNo()),'YYYY.MM') as YearMonth
FROM [https://community.qlik.com/thread/225265] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 5))))
While IterNo()<13;

hope this helps

regards

Marco

[#4]

With your example I got it to work.

Thanks for your help!!