Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sibrulotte
Creator III
Creator III

Generating missing data: multiple values per date

Hi,

there are great tutorials on how to generate a single value per date that are missing. One rate per date is not my case here.

I have a benchmark that has multiple CUSIPs per date. But my saterdays and sundays aren't populated.

I'm trying to get my script to fill Saterday's list with friday's list, and sunday's with saterday's/friday's.

Here's my scrip so far:

Benchmark_tmp:

LOAD *

, Date  & CUSIP_BENCHMARK as KEY1

INLINE [

    Date, CUSIP_BENCHMARK

    2015-02-14, A

    2015-02-14, B

    2015-02-14, C

    2015-02-14, D

    2015-02-14, E

    2015-02-14, F

    2015-02-17, A

    2015-02-17, C

    2015-02-17, D

    2015-02-17, E

    2015-02-17, F

    2015-02-17, G

]

;

MinMaxDate:

Load Min(Date) as MinDate,

Max(Date) as MaxDate

resident Benchmark_tmp;

Let vMinDateIndices = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDateIndices = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

Join (Benchmark_tmp)

Load

Date(recno()+$(vMinDateIndices)) as Date

Autogenerate vMaxDateIndices - vMinDateIndices;

Benchmark:

NoConcatenate

Load Date

, If( IsNull(KEY1), Peek( KEY1), KEY1 ) as KEY1

, If( IsNull(CUSIP_BENCHMARK), Peek( CUSIP_BENCHMARK), CUSIP_BENCHMARK ) as CUSIP_BENCHMARK

Resident Benchmark_tmp

Order By Date, CUSIP_BENCHMARK ;

Drop Table Benchmark_tmp;

I know the answer is going to lie with the KEY1 component, but I'm just not quite getting there.

I only get friday's last value for saterday and sunday. I want to get the whole list...

Please help?

1 Solution

Accepted Solutions
daveamz
Partner - Creator III
Partner - Creator III

Hi Simon,

Can you post some sample data? Only the fileds: Date and CUSIP_BENCHMARK.

Do you want to populate missing dates with data from the last previous known date?

David

UPDATE: See if logic behind the attached script works for your data. It might run real slow on very large datasets.

View solution in original post

4 Replies
daveamz
Partner - Creator III
Partner - Creator III

Hi Simon,

If you are missing only Saturdays and Sundays then you can use something like:

Benchmark_tmp:

LOAD *

INLINE [

    Date, CUSIP_BENCHMARK

    2015-02-06, A

    2015-02-06, B

    2015-02-06, C

    2015-02-06, D

    2015-02-06, E

    2015-02-06, F

    2015-02-13, A

    2015-02-13, B

    2015-02-13, C

    2015-02-13, D

    2015-02-13, E

    2015-02-13, F

    2015-02-16, A

    2015-02-16, C

    2015-02-16, D

    2015-02-16, E

    2015-02-16, F

    2015-02-16, G

];

//Add Saturday

CONCATENATE(Benchmark_tmp)

LOAD Date(Date+1) as Date, //Add as Saturday

     CUSIP_BENCHMARK

RESIDENT Benchmark_tmp WHERE WeekDay(Date) = 4;

//Add Sunday

CONCATENATE(Benchmark_tmp)

LOAD Date(Date+2) as Date, //Add as Sunday

     CUSIP_BENCHMARK

RESIDENT Benchmark_tmp WHERE WeekDay(Date) = 4;

Regards,

David

sibrulotte
Creator III
Creator III
Author

that's a good plan. Somehow my weekday result is in letters, but I made it work.

thanks

sibrulotte
Creator III
Creator III
Author

Apparently, there are more than just saterdays and sundays missing, and it's sporadic...

Back to the drawing board.

daveamz
Partner - Creator III
Partner - Creator III

Hi Simon,

Can you post some sample data? Only the fileds: Date and CUSIP_BENCHMARK.

Do you want to populate missing dates with data from the last previous known date?

David

UPDATE: See if logic behind the attached script works for your data. It might run real slow on very large datasets.