Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

arjanijlenhave
New Contributor

Creating missing dates

Hi all,

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

CustomerPolisNumberJobDateAmount
123456123456-VPB-11-1-2015150
654321654321-COP-11-1-2015300
654321654321-COP-11-4-2015250
654321654321-COP-11-7-2015175
654321654321-COP-11-10-2015450
456789456789-TTP-11-7-2015750
999556999556-TTP-11-1-2015150
999556999556-TTP-11-2-2015150
999556999556-TTP-11-3-2015150
999556999556-TTP-11-4-2015150
999556999556-TTP-11-5-2015150
999556999556-TTP-11-6-2015150
999556999556-TTP-11-7-2015150
999556999556-TTP-11-8-2015150
999556999556-TTP-11-9-2015150
999556999556-TTP-11-10-2015150
999556999556-TTP-11-11-2015150

I want to create the following table data:

CustomerPolisNumberJobDateAmount
123456123456-VPB-11-1-2015150
123456123456-VPB-11-2-20150
123456123456-VPB-11-3-20150
123456123456-VPB-11-4-20150
123456123456-VPB-11-5-20150
123456123456-VPB-11-6-20150
123456123456-VPB-11-7-20150
123456123456-VPB-11-8-20150
123456123456-VPB-11-9-20150
123456123456-VPB-11-10-20150
123456123456-VPB-11-11-20150
654321654321-VPB-11-1-2015300
654321654321-VPB-11-2-20150
654321654321-VPB-11-3-20150
654321654321-VPB-11-4-2015250
654321654321-VPB-11-5-20150
654321654321-VPB-11-6-20150
654321654321-VPB-11-7-2015175
654321654321-VPB-11-8-20150
654321654321-VPB-11-9-20150
654321654321-VPB-11-10-2015450
654321654321-VPB-11-11-20150
456789456789-TTP-11-1-20150
456789456789-TTP-11-2-20150
456789456789-TTP-11-3-20150
456789456789-TTP-11-4-20150
456789456789-TTP-11-5-20150
456789456789-TTP-11-6-20150
456789456789-TTP-11-7-2015750
456789456789-TTP-11-8-20150
456789456789-TTP-11-9-20150
456789456789-TTP-11-10-20150
456789456789-TTP-11-11-20150
999556999556-TTP-11-1-2015150
999556999556-TTP-11-2-2015150
999556999556-TTP-11-3-2015150
999556999556-TTP-11-4-2015150
999556999556-TTP-11-5-2015150
999556999556-TTP-11-6-2015150
999556999556-TTP-11-7-2015150
999556999556-TTP-11-8-2015150
999556999556-TTP-11-9-2015150
999556999556-TTP-11-10-2015150
999556999556-TTP-11-11-2015150

Can you help me?

Best regards,

Arjan

1 Solution

Accepted Solutions
martijn
New Contributor III

Re: Creating missing dates

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;

4 Replies
miguelbraga
Valued Contributor III

Re: Creating missing dates

Hey there,

Have you tried to make a Master Calendar?

Thanks,

MB

mark6505
Valued Contributor III

Re: Creating missing dates

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

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
New Contributor III

Re: Creating missing dates

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;

Community Browser