Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

How can I autogenerate rows from year total?

Hi Community

I am pretty new to Qlikview and need some help.

I have a table, containing totals per year :

YearAgentCustomerValue per Year
2016PaulIBD2000
2016PaulABC35435
2016Petersadasds23421
....

And I have a table containing a factor per month:

YearMonthFactor
2016.010.05
2016.020.03
......
2016.120.04

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

YearMonthAgentCustomerValue per Month
2016.01PaulIBD100 (<---- this is "value per year" * "factor")
2016.02PaulIBD60
2016.03..........
2016.01Petersadasds1171.05
......

How can I generate this?

Thank you very much for your help.

Tags (3)
1 Solution

Accepted Solutions

Re: How can I autogenerate rows from year total?

Hi,

one solution might be:

QlikCommunity_Thread_225265_Pic1.JPG

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

3 Replies

Re: How can I autogenerate rows from year total?

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;

Re: How can I autogenerate rows from year total?

Hi,

one solution might be:

QlikCommunity_Thread_225265_Pic1.JPG

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

Not applicable

Re: How can I autogenerate rows from year total?

With your example I got it to work.

Thanks for your help!!

Community Browser