Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
bengan74
New Contributor

Add monthly value to dates

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:

YearMonthShopHitRate
2014jan10066%
2014feb10068%
2014mar10065%
2014apr10068%
2014may10067%
2014jun10068%
2014jul10064%
2014aug10067%
2014sep10068%
2014oct10069%
2014nov10068%
2014Dec10066%

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:

DateShopHitRate
2014-01-3010066%
2014-01-3110066%
2014-02-0110068%
2014-02-0210068%

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

1 Solution

Accepted Solutions

Re: Add monthly value to dates

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


talk is cheap, supply exceeds demand
11 Replies
venkatasreekant
Valued Contributor

Re: Add monthly value to dates

Use the code like

Load

Date,

Shop,

HitRate

Resident Tablename

Here table name means first table table name..

bengan74
New Contributor

Re: Add monthly value to dates

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

venkatasreekant
Valued Contributor

Re: Add monthly value to dates

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

bengan74
New Contributor

Re: Add monthly value to dates

Hi Venkata,

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

QV.jpg

Brg Johan

Employee
Employee

Re: Add monthly value to dates

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

Re: Add monthly value to dates

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


talk is cheap, supply exceeds demand

Re: Add monthly value to dates

Hi,

From which table you are generating Date??

If possible share sample app?

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Add monthly value to dates

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

venkatasreekant
Valued Contributor

Re: Add monthly value to dates

Hi Johan,

Remove the Drop Table Temp from script....

Community Browser