Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hey there,
Have you tried to make a Master Calendar?
Thanks,
MB
Hi,
I reckon you will be looking at an interval match
take a look at the linked doc.
Interval Match Feature/Function
Mark
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;
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;