Creating missing dates

[Anonymous]

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

[miguelbraga]

Hey there,

Have you tried to make a Master Calendar?

Thanks,

MB

[Mark_Little]

Hi,

I reckon you will be looking at an interval match

take a look at the linked doc.

Interval Match Feature/Function

Mark

[settu_periasamy]

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;

[martijn · Accepted solution]

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;