Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bengan74
Contributor III
Contributor III

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
Gysbert_Wassenaar

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

View solution in original post

11 Replies
Anonymous
Not applicable

Use the code like

Load

Date,

Shop,

HitRate

Resident Tablename

Here table name means first table table name..

bengan74
Contributor III
Contributor III
Author

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
Not applicable

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

bengan74
Contributor III
Contributor III
Author

Hi Venkata,

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

QV.jpg

Brg Johan

Clever_Anjos
Employee
Employee

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

Gysbert_Wassenaar

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
PrashantSangle

Hi,

From which table you are generating Date??

If possible share sample app?

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
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
Not applicable

Hi Johan,

Remove the Drop Table Temp from script....