Discussion Board for collaboration on QlikView Scripting.
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:
, Date & CUSIP_BENCHMARK as KEY1
Load Min(Date) as MinDate,
Max(Date) as MaxDate
Let vMinDateIndices = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDateIndices = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
Date(recno()+$(vMinDateIndices)) as Date
Autogenerate vMaxDateIndices - vMinDateIndices;
, If( IsNull(KEY1), Peek( KEY1), KEY1 ) as KEY1
, If( IsNull(CUSIP_BENCHMARK), Peek( CUSIP_BENCHMARK), CUSIP_BENCHMARK ) as CUSIP_BENCHMARK
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...
Go to Solution.
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?
UPDATE: See if logic behind the attached script works for your data. It might run real slow on very large datasets.
If you are missing only Saturdays and Sundays then you can use something like:
LOAD Date(Date+1) as Date, //Add as Saturday
RESIDENT Benchmark_tmp WHERE WeekDay(Date) = 4;
LOAD Date(Date+2) as Date, //Add as Sunday
that's a good plan. Somehow my weekday result is in letters, but I made it work.
Apparently, there are more than just saterdays and sundays missing, and it's sporadic...
Back to the drawing board.