topic Re: Add monthly value to dates in Water Cooler

Add monthly value to dates

bengan74 — Thu, 12 Jun 2014 11:07:39 GMT

Hi everyone!

I have a table with target values for HitRate for each store, month and year that I like to add to each date within the month.

TempHitrate:

Year	Month	Shop	HitRate
2014	jan	1006	6%
2014	feb	1006	8%
2014	mar	1006	5%
2014	apr	1006	8%
2014	may	1006	7%
2014	jun	1006	8%
2014	jul	1006	4%
2014	aug	1006	7%
2014	sep	1006	8%
2014	oct	1006	9%
2014	nov	1006	8%
2014	Dec	1006	6%

I'd like to create a table with new field, Date (generated in the same way as in QV calender-script), and add Shop and the value from the field HitRate in the table TempHitrate above so the result looks like this:

Hitrate:

Date	Shop	HitRate
2014-01-30	1006	6%
2014-01-31	1006	6%
2014-02-01	1006	8%
2014-02-02	1006	8%

So the field Date matches with the correct month and year in the first table, and adds the value for HitRate.

Is there anyone who know how to solve this I'm very greatfull!

Brg Johan

Re: Add monthly value to dates

Anonymous — Thu, 12 Jun 2014 11:09:46 GMT

Use the code like

Load

Date,

Shop,

HitRate

Resident Tablename

Here table name means first table table name..

Re: Add monthly value to dates

bengan74 — Thu, 12 Jun 2014 12:13:40 GMT

Hi Venkata, and thank you for the reply!

As I don't have the field Date in the first table I need to generate this in another way and at the same time match it with Year and Month from the first table.

//Johan

Re: Add monthly value to dates

Anonymous — Thu, 12 Jun 2014 12:15:10 GMT

By using the resident table we can get that......

Re: Add monthly value to dates

bengan74 — Thu, 12 Jun 2014 14:32:02 GMT

Hi Venkata,

Did you mean like this? I didn't get it to work

Brg Johan

Re: Add monthly value to dates

Clever_Anjos — Thu, 12 Jun 2014 14:40:53 GMT

You only have year and month? Does not have a date into "targest per manad.xlsx"?

Re: Add monthly value to dates

Gysbert_Wassenaar — Thu, 12 Jun 2014 14:43:51 GMT

Data:
LOAD Year, Month, Shop, HitRate, date(Date+iterno()-1,'YYYY-MM-DD') as Date
while Date+iterno()-1<= MonthEnd(Date);
LOAD *, date#(Year&Month,'YYYYMMM') as Date INLINE [
    Year, Month, Shop, HitRate
    2014, jan, 1006, 6%
    2014, feb, 1006, 8%
    2014, mar, 1006, 5%
    2014, apr, 1006, 8%
    2014, may, 1006, 7%
    2014, jun, 1006, 8%
    2014, jul, 1006, 4%
    2014, aug, 1006, 7%
    2014, sep, 1006, 8%
    2014, oct, 1006, 9%
    2014, nov, 1006, 8%
    2014, dec, 1006, 6%
];

See attached qvw

Re: Add monthly value to dates

PrashantSangle — Thu, 12 Jun 2014 14:49:38 GMT

Hi,

From which table you are generating Date??

If possible share sample app?

Regards,

Re: Add monthly value to dates

its_anandrjs — Thu, 12 Jun 2014 15:36:36 GMT

You need the master calendar then connect it with your source table with the Year and Month Key see the load script below, You can join this two table also

---------------------------------

tmpSource:

LOAD Date#(Year,'YYYY') as Year,Date#(Capitalize(Month),'MMM') as Month,

Date#(Year,'YYYY')&'-'&Date#(Capitalize(Month),'MMM') as %Key,Shop,HitRate;

LOAD * Inline

[

Year, Month, Shop, HitRate

2014, jan, 1006, 6%

2014, feb, 1006, 8%

2014, mar, 1006, 5%

2014, apr, 1006, 8%

2014, may, 1006, 7%

2014, jun, 1006, 8%

2014, jul, 1006, 4%

2014, aug, 1006, 7%

2014, sep, 1006, 8%

2014, oct, 1006, 9%

2014, nov, 1006, 8%

2014, Dec, 1006, 6%

];

NoConcatenate

Source:

LOAD * Resident tmpSource

Order by Year,Month Asc;

DROP Table tmpSource;

Let varMinDate = Num(MakeDate(Year( Peek('Year',0,'Source') ), Month(Date#(Capitalize(Peek('Month',0,'Source')),'MMM'))));

Let varMaxDate = Num(MakeDate(Year( Peek('Year',-1,'Source') ), Month(Date#(Capitalize(Peek('Month',-1,'Source')),'MMM'))));

DROP Fields Year,Month;

Calendar:

LOAD

Date($(varMinDate)+IterNo()-1,'YYYY-MM-DD' ) AS Datefield,

Year(date($(varMinDate)+IterNo()-1)) as Year,

Year(date($(varMinDate)+IterNo()-1))&'-'&Month(date($(varMinDate)+IterNo()-1)) as %Key,

Month(date($(varMinDate)+IterNo()-1)) as Month

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Re: Add monthly value to dates

Anonymous — Fri, 13 Jun 2014 05:47:22 GMT

Hi Johan,

Remove the Drop Table Temp from script....

Re: Add monthly value to dates

Anonymous — Fri, 13 Jun 2014 06:22:52 GMT

Something like below,

Re: Add monthly value to dates

bengan74 — Mon, 16 Jun 2014 08:24:18 GMT

Thank you all for great help with this issue!

/Brg Johan