topic Re: Creating missing dates in QlikView

Creating missing dates

Anonymous — Wed, 11 Nov 2015 11:10:47 GMT

Hi all,

A table has the following data and the current date is 11-11-2015.

Customer	PolisNumber	JobDate	Amount
123456	123456-VPB-1	1-1-2015	150
654321	654321-COP-1	1-1-2015	300
654321	654321-COP-1	1-4-2015	250
654321	654321-COP-1	1-7-2015	175
654321	654321-COP-1	1-10-2015	450
456789	456789-TTP-1	1-7-2015	750
999556	999556-TTP-1	1-1-2015	150
999556	999556-TTP-1	1-2-2015	150
999556	999556-TTP-1	1-3-2015	150
999556	999556-TTP-1	1-4-2015	150
999556	999556-TTP-1	1-5-2015	150
999556	999556-TTP-1	1-6-2015	150
999556	999556-TTP-1	1-7-2015	150
999556	999556-TTP-1	1-8-2015	150
999556	999556-TTP-1	1-9-2015	150
999556	999556-TTP-1	1-10-2015	150
999556	999556-TTP-1	1-11-2015	150

I want to create the following table data:

Customer	PolisNumber	JobDate	Amount
123456	123456-VPB-1	1-1-2015	150
123456	123456-VPB-1	1-2-2015	0
123456	123456-VPB-1	1-3-2015	0
123456	123456-VPB-1	1-4-2015	0
123456	123456-VPB-1	1-5-2015	0
123456	123456-VPB-1	1-6-2015	0
123456	123456-VPB-1	1-7-2015	0
123456	123456-VPB-1	1-8-2015	0
123456	123456-VPB-1	1-9-2015	0
123456	123456-VPB-1	1-10-2015	0
123456	123456-VPB-1	1-11-2015	0
654321	654321-VPB-1	1-1-2015	300
654321	654321-VPB-1	1-2-2015	0
654321	654321-VPB-1	1-3-2015	0
654321	654321-VPB-1	1-4-2015	250
654321	654321-VPB-1	1-5-2015	0
654321	654321-VPB-1	1-6-2015	0
654321	654321-VPB-1	1-7-2015	175
654321	654321-VPB-1	1-8-2015	0
654321	654321-VPB-1	1-9-2015	0
654321	654321-VPB-1	1-10-2015	450
654321	654321-VPB-1	1-11-2015	0
456789	456789-TTP-1	1-1-2015	0
456789	456789-TTP-1	1-2-2015	0
456789	456789-TTP-1	1-3-2015	0
456789	456789-TTP-1	1-4-2015	0
456789	456789-TTP-1	1-5-2015	0
456789	456789-TTP-1	1-6-2015	0
456789	456789-TTP-1	1-7-2015	750
456789	456789-TTP-1	1-8-2015	0
456789	456789-TTP-1	1-9-2015	0
456789	456789-TTP-1	1-10-2015	0
456789	456789-TTP-1	1-11-2015	0
999556	999556-TTP-1	1-1-2015	150
999556	999556-TTP-1	1-2-2015	150
999556	999556-TTP-1	1-3-2015	150
999556	999556-TTP-1	1-4-2015	150
999556	999556-TTP-1	1-5-2015	150
999556	999556-TTP-1	1-6-2015	150
999556	999556-TTP-1	1-7-2015	150
999556	999556-TTP-1	1-8-2015	150
999556	999556-TTP-1	1-9-2015	150
999556	999556-TTP-1	1-10-2015	150
999556	999556-TTP-1	1-11-2015	150

Can you help me?

Best regards,

Arjan

Re: Creating missing dates

miguelbraga — Wed, 11 Nov 2015 12:35:28 GMT

Hey there,

Have you tried to make a Master Calendar?

Thanks,

Re: Creating missing dates

Mark_Little — Wed, 11 Nov 2015 12:39:29 GMT

Hi,

I reckon you will be looking at an interval match

take a look at the linked doc.

Interval Match Feature/Function

Mark

Re: Creating missing dates

settu_periasamy — Wed, 11 Nov 2015 12:45:45 GMT

Hi,

check the below script. may be helps. May Date format is (M/D/YYYY)

T1:

LOAD * INLINE [

Customer, PolisNumber, JobDate, Amount

123456, 123456-VPB-1, 1/1/2015, 150

654321, 654321-COP-1, 1/1/2015, 300

654321, 654321-COP-1, 4/1/2015, 250

654321, 654321-COP-1, 7/1/2015, 175

654321, 654321-COP-1, 10/1/2015, 450

456789, 456789-TTP-1, 7/1/2015, 750

999556, 999556-TTP-1, 1/1/2015, 150

999556, 999556-TTP-1, 2/1/2015, 150

999556, 999556-TTP-1, 3/1/2015, 150

999556, 999556-TTP-1, 4/1/2015, 150

999556, 999556-TTP-1, 5/1/2015, 150

999556, 999556-TTP-1, 6/1/2015, 150

999556, 999556-TTP-1, 7/1/2015, 150

999556, 999556-TTP-1, 8/1/2015, 150

999556, 999556-TTP-1, 9/1/2015, 150

999556, 999556-TTP-1, 10/1/2015, 150

999556, 999556-TTP-1, 11/1/2015, 150

];

NoConcatenate

T2:

Load Distinct Customer,PolisNumber Resident T1;

Join(T2)

Load * Inline [

JobDate

1/1/2015

2/1/2015

3/1/2015

4/1/2015

5/1/2015

6/1/2015

7/1/2015

8/1/2015

9/1/2015

10/1/2015

11/1/2015

];

Left Join(T2)

LOAD * Resident T1;

DROP Table T1;

Re: Creating missing dates

martijn — Thu, 12 Nov 2015 15:44:15 GMT

On date level:

T1:
LOAD * INLINE [
Customer, PolisNumber, JobDate, Amount
123456, 123456-VPB-1, 1-1-2015, 150
654321, 654321-COP-1, 1-1-2015, 300
654321, 654321-COP-1, 1-4-2015, 250
654321, 654321-COP-1, 1-7-2015, 175
654321, 654321-COP-1, 1-10-2015, 450
456789, 456789-TTP-1, 1-7-2015, 750
999556, 999556-TTP-1, 1-1-2015, 150
999556, 999556-TTP-1, 1-2-2015, 150
999556, 999556-TTP-1, 1-3-2015, 150
999556, 999556-TTP-1, 1-4-2015, 150
999556, 999556-TTP-1, 1-5-2015, 150
999556, 999556-TTP-1, 1-6-2015, 150
999556, 999556-TTP-1, 1-7-2015, 150
999556, 999556-TTP-1, 1-8-2015, 150
999556, 999556-TTP-1, 1-9-2015, 150
999556, 999556-TTP-1, 1-10-2015, 150
999556, 999556-TTP-1, 1-11-2015, 150
]
;

T2:
NoConcatenate load
*,
Customer&'|'&PolisNumber as ID
Resident T1
Order by
PolisNumber asc,
JobDate Desc ;

Drop table T1;

T3:
load *, if(previous(ID)=ID, previous(JobDate),ToDay()) as NextJobDate
Resident T2
order by ID, JobDate desc;

drop table T2;

Resultaat:
load
ID,
Customer,
PolisNumber,
JobDate,
date(JobDate + IterNo() -1) as Date,
If(JobDate=date(JobDate + IterNo() -1), Amount,0) as Amount
Resident T3
while JobDate + IterNo() - 1 < NextJobDate
order by
ID,
JobDate;

drop table T3;