topic Re: generate in script 12 monthly values from a single year value in QlikView

generate in script 12 monthly values from a single year value

omyahamburg — Fri, 22 Feb 2013 12:22:22 GMT

Dear All

I have a table with a lots of Years and a values.

Eg.

Year value

2012 1200

...

I would like to generate within my script a table which

splits these values into the months:

Eg:

YearMonth value

2012-01	100
2012-02	100
2012-03	100
2012-04	100
2012-05	100
2012-06	100
2012-07	100
2012-08	100
2012-09	100
2012-10	100
2012-11	100
2012-12	100

Can anyone tell me how to generate this ?

I hope an easy task for the experts, but I am still beginner

and appreciate your help.

Cheers

Joerg

Re: generate in script 12 monthly values from a single year value

manideep78 — Fri, 22 Feb 2013 12:29:56 GMT

give us an idea how you want to split your values into months? What values field actually consists of?

Re: generate in script 12 monthly values from a single year value

omyahamburg — Fri, 22 Feb 2013 12:35:29 GMT

Any found Year value must be divided by 12 and the YearMonth must be created from 01-12 for the corresponding Year.

Re: generate in script 12 monthly values from a single year value

manideep78 — Fri, 22 Feb 2013 12:43:02 GMT

Any found year value must be divided by 12. Thats fine.

But how to get 01-12? what does it exactly mean?

Provide some sample data.

Your data has only year and values. so how you want to generate YearMonth?

Re: generate in script 12 monthly values from a single year value

omyahamburg — Fri, 22 Feb 2013 12:49:05 GMT

As I wrote, I have a Year value eg. 1200

This value needs to divided by 12 to get eg. 100 each and to each of the new value the month from January=01 till December=12 should be created.

Re: generate in script 12 monthly values from a single year value

julian_rodriguez — Fri, 22 Feb 2013 12:56:02 GMT

Hello Joerg

This article solves the first question: populate the table with the dates between the first and the last:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

With this table, then you may use RangeAvg() and Peek() functions to make the average.

But first of all, populate your data.

Regards

Re: generate in script 12 monthly values from a single year value

Fri, 22 Feb 2013 12:58:30 GMT

Hi,

Please find the atatched doc.

Regards,

Yaseen

Re: generate in script 12 monthly values from a single year value

omyahamburg — Fri, 22 Feb 2013 13:02:57 GMT

Hi Julian

This looks good. I will go through this example for my case.

Thx a lot and have a good weekend.

Re: generate in script 12 monthly values from a single year value

jagannalla — Fri, 22 Feb 2013 13:03:42 GMT

Hello,

You can try this, it is very simple and faster.

Temp:
LOAD * INLINE [
    Year, Value
    2012, 1200
    2013, 1200
];
CalenderVal:
LOAD Date(MakeDate(Year, IterNo()),'YYYY-MM') as YrMonth,(Value/12) as Val
Resident Temp While IterNo()<=12;

Hope it helps you

Cheers!!

Jagan

Re: generate in script 12 monthly values from a single year value

omyahamburg — Fri, 22 Feb 2013 13:05:38 GMT

Hi Yaseen

I never thought that it can be so easy. You made my day.

But as I am always eager to learn I'll also go through the exercise from Julian.

Thx a lot and have a good weekend.

Joerg

Re: generate in script 12 monthly values from a single year value

omyahamburg — Fri, 22 Feb 2013 13:09:19 GMT

Dear Jagan

Also a nice solution for me.

Thanks a lot.

Joerg

Re: generate in script 12 monthly values from a single year value

Priyanka_Mani — Mon, 08 Jul 2024 08:14:16 GMT

Hi,

Could you please share the code here. Is that using IterNo() function?

Thanks!