Add monthly value to dates

[bengan74]

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

[Anonymous]

Use the code like

Load

Date,

Shop,

HitRate

Resident Tablename

Here table name means first table table name..

[bengan74]

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

[Anonymous]

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

[bengan74]

Hi Venkata,

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

Brg Johan

[Clever_Anjos]

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

[Gysbert_Wassenaar · Accepted solution]

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

[PrashantSangle]

Hi,

From which table you are generating Date??

If possible share sample app?

Regards,

[its_anandrjs]

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);

[Anonymous]

Hi Johan,

Remove the Drop Table Temp from script....