topic Re: filling blank dates with previous data in QlikView

filling blank dates with previous data

Sat, 29 Dec 2012 07:49:23 GMT

CLIENT NO	CLIENT NAME	DATE	CCY	PROD TYPE	AMOUNT

010000000	ASDFG	1/1/2012	EUR	LRP	56,987
010000001	ASDFGDSF	2/1/2012	USD	FGT	54,785
010000002	ASDFASDFASDF	3/1/2012	USD	BGO	62,314
011000003	ASDSASDE	4/1/2012	USD	PBG	56,987
011000004	SADEDSA	6/1/2012	USD	PBG	25,558
011000005	ASEDF	7/1/2012	USD	PBG	69,874
011000006	ASDFTED	8/1/2012	USD	PBG	58,461
011000007	ADSFE	12/1/2012	USD	BGO	56,987
011000008	FAFOOOASF	13/1/2012	USD	BGO	54,785
011000009	ADSFLLLL	14/1/2012	USD	BGO	62,314
011000010	ADLFOPP	15/1/2012	USD	BGO	23,268
011000011	AFQER	16/1/2012	USD	BGO	23,654
011000012	INTER LTD.	17/1/2012	USD	BGO	2,828
011000013	VSAFD	23/1/2012	USD	BGO	56,987
011000314	ASDFRRYGFS	24/1/2012	EUR	BGO	54,785
011200015	ADSFACVR	25/1/2012	SAR	BGO	62,314
011200216	ASDFEFCC	26/1/2012	SAR	BGO	23,268
011200517	ADSFVHT	27/1/2012	USD	PBG	56,987
011200618	AQEWER	28/1/2012	USD	PBG	54,785
011200619	CVREFASDF	29/1/2012	USD	PBG	62,314
011200620	ASDFCWE	30/1/2012	USD	PBG	56,987

I have a following data, in this data i have some dates missing like 5/1/2012 , 9-11/1/2012, 18-22/1/2012 these are the holydays and there is no data in them what i want is that in these dates the previous date customers data should be copied. i need the script for. if there is a difference of one date like 5/1/2012 in that i need the data of 4/1/2012 and from 9-11/1/2012 i need the data of 8/1/2012

i have used date dimension too.

Re: filling blank dates with previous data

Anonymous — Sat, 29 Dec 2012 08:38:14 GMT

I think you can alter the script in the following post that accumulates data based on a combination of dimensions and dates (http://community.qlik.com/message/16104#16104).

If you reload your table with an 'Order By Date Desc' clause, testing for an AccumulationKey (just date, or date plus relevant dimensions) you should be able to determine when a date is missing and when you have to roll over to the previous date.

Jonathan

Re: filling blank dates with previous data

Gysbert_Wassenaar — Sat, 29 Dec 2012 10:34:03 GMT

See attached qvw for a possible solution.

Re: filling blank dates with previous data

Sun, 10 Feb 2013 19:20:32 GMT

Thanks Gysbert for the reply it is working good in one customer but what if there are more then one records in that date like

CLIENT NO, CLIENT NAME, DATE, CCY, PROD TYPE, AMOUNT

011000003, ASDSASDE, 4/1/2012, USD, PBG, "56,987"

010000001, ASDFGDSF, 4/1/2012, USD, FGT, "54,785"

011000007, ADSFE, 4/1/2012, USD, BGO, "56,987"

011000008, FAFOOOASF, 4/1/2012, USD, BGO, "54,785"

011000009, ADSFLLLL, 4/1/2012, USD, BGO, "62,314"

i have tried and did modifications in the script but it didn't work can you please help me in this