topic How to Fill Continous Date in Qlikview ???? in QlikView

How to Fill Continous Date in Qlikview ????

Anonymous — Sat, 19 Nov 2011 16:00:31 GMT

Hello All ,

I have a Scenario Where , I have date in such manner

Country	CALMONTH	Product Count
USA	Aug-01	5
USA	Oct-01	8
USA	Nov-01	10
USA	Jan-11	15
UK	Aug-01	7
UK	Oct-01	8
UK	Nov-01	10
UK	Jan-01	13

and i want data this format in

Country	CALMONTH	Product Count
USA	Aug-01	5
USA	Sep-01	5
USA	Oct-01	8
USA	Nov-01	8
USA	Dec-01	10
USA	Jan-02	10
USA	Feb-02	15
UK	Aug-01	7
UK	Sep-01	7
UK	Oct-01	8
UK	Nov-01	10
UK	Dec-01	10
UK	Jan-02	13
UK	Feb-02	13

Please Help me

Thanxs in Advance

How to Fill Continous Date in Qlikview ????

Sat, 19 Nov 2011 18:08:12 GMT

Some doubts:

1. In the first data set for US the month is Jan-11. Is that a typo?

2. In the first data set there is no Sep-01. Why does it appear in the second data set?

3. Why does Feb-02 appear in the second data set but Mar-02 does not appear in the second data set?

4. Why is Jan-02 product count not 15.

Also its good to post a qvw file.

Best Regards,
Vishal

How to Fill Continous Date in Qlikview ????

Anonymous — Sun, 20 Nov 2011 07:32:37 GMT

Sotty Guysssits a typo mistake

My data is in this format

Country	CALMONTH	Product Count
USA	Aug-01	5
USA	Oct-01	8
USA	Nov-01	10
USA	Jan-02	15
UK	Aug-01	7
UK	Oct-01	8
UK	Nov-01	10
UK	Jan-02	13

and i want data this format in

Country	CALMONTH	Product Count
USA	Aug-01	5
USA	Sep-01	5
USA	Oct-01	8
USA	Nov-01	8
USA	Dec-01	10
USA	Jan-02	10
USA	Feb-02	15
UK	Aug-01	7
UK	Sep-01	7
UK	Oct-01	8
UK	Nov-01	10
UK	Dec-01	10
UK	Jan-02	13
UK	Feb-02	13

please help me out and thanxs in advance

Re: How to Fill Continous Date in Qlikview ????

swuehl — Sun, 20 Nov 2011 13:23:43 GMT

In your result table for USA, Nov01, it should be 10 instead of 8, right?

Please look at attached sample file. I used two approaches (you need only one of them), I think most simple is:

INPUT:

LOAD Country,

Date(Date#(CALMONTH,'MMM-YY'),'MMM-YY') as CALMONTH,

[Product Count]

FROM

[http://community.qlik.com/thread/39299]

(html, codepage is 1252, embedded labels, table is @3);

INPUT2:

LOAD Country as Country2, CALMONTH as CALMONTH_START, [Product Count] as [Product Count2],

if(peek(Country2)=Country, date(peek(CALMONTH_START)-1,'MMM-YY'), makedate(2002,02,28) ) as CALMONTH_END

resident INPUT order by Country, CALMONTH desc;

RESULT2:

LOAD

Date(addmonths(CALMONTH_START,iterno()-1),'MMM-YY') as CALMONTH2b

,Country2 as Country2b

,[Product Count2] as [Product Count2b]

resident INPUT2 WHILE addmonths(CALMONTH_START,iterno()-1) <= CALMONTH_END;

I think you could merge INPUT2 and INPUT loads, if you read in from your original data source.

The basic idea is to generate a end date for each record (just a little smaller than the next entry date for the next record of this country. Then use a while loop in the following load to create a record per country and product count from entry date to end date.

You could also use another approach using intervalmatch after you have created the two dates, but that seems to be a little more complex.

Hope this helps,

Stefan

How to Fill Continous Date in Qlikview ????

Anonymous — Mon, 21 Nov 2011 06:54:35 GMT

Thanxs a Lot

Stefan

ur Solution Really Worked Out !!!!!!!!

How to Fill Continous Date in Qlikview ????

Anonymous — Tue, 22 Nov 2011 09:31:07 GMT

There is slight Change in Requirement

data is in this format

Country	Product	YearMonth
US	A	200101
US	C	200104
US	D	200106
UK	B	200103
UK	E	200106
UK	F	200107

	and now I want data in

Country	Product	YearMonth
US	A	200101
US	A	200102
US	A	200103
US	A	200104
US	C	200104
US	A	200105
US	C	200105
US	A	200106
US	C	200106
US	D	200106
UK	B	200103
UK	B	200104
UK	B	200105
UK	B	200106
UK	E	200106
UK	B	200107
UK	E	200107
UK	F	200107


Please Help Me Out thnxs in advance

How to Fill Continous Date in Qlikview ????

swuehl — Tue, 22 Nov 2011 11:02:52 GMT

Yes, that's a little different, if I understood correctly, like this:

Countries:

LOAD Country, Product, Date#(YearMonth,'YYYYMM') as YearMonth INLINE [

Country, Product, YearMonth

US, A, 200101

US, C, 200104

US, D, 200106

UK, B, 200103

UK, E, 200106

UK, F, 200107

];

Extrema:

LOAD Country, min(YearMonth) as minYM, max(YearMonth) as maxYM

resident Countries Group by Country;

Result:

NOCONCATENATE LOAD Country, Product, Date(addmonths(YearMonth,iterno()-1),'YYYYMM') as YearMonth

resident Countries while addmonths(YearMonth,iterno()-1)<=Lookup('maxYM','Country',Country,'Extrema') ;

drop tables Countries, Extrema;

How to Fill Continous Date in Qlikview ????

Anonymous — Tue, 22 Nov 2011 12:16:49 GMT

The End Date of Both US and UK should be todays YearMonth

how will it come ?

How to Fill Continous Date in Qlikview ????

swuehl — Tue, 22 Nov 2011 12:29:41 GMT

Ok,

then it's even a little bit more simple:

Countries:

LOAD Country, Product, Date#(YearMonth,'YYYYMM') as YearMonth INLINE [

Country, Product, YearMonth

US, A, 200101

US, C, 200104

US, D, 200106

UK, B, 200103

UK, E, 200106

UK, F, 200107

];

Result:

NOCONCATENATE LOAD Country, Product, Date(addmonths(YearMonth,iterno()-1),'YYYYMM') as YearMonth

resident Countries while addmonths(YearMonth,IterNo()-1) <= today();

drop tables Countries;

How to Fill Continous Date in Qlikview ????

Anonymous — Wed, 23 Nov 2011 13:40:59 GMT

Thnxs a Lot

Stefan

4r helping me out

ur solution is just amazing