Providing Missing Records on Data Load based on Date Gaps
Hello !
Need some help to build my data load here.
The situation is similar to this one. I have a STOCKS database with 4 fields :
- STOCK TICKER (or code/name)
- STOCK DATE
- STOCK QUANTITY (# of stocks)
- STOCK VALUE (on the DATE above)
This database has many different stock codes and many dates.
I need to calculate the TOTAL value which is the sum of (STOCK QUANTITY * STOCK VALUE) per DATE. This will go into a graph of the TOTAL value per day.
The problem ? Not all STOCKS have trades every day. For the days we dont have a RECORD on the database for a particular STOCK, we should repeat the day before. Because if not, we have GAPS on the graph !
Say for instance a MICRO stock code that has records for days 18/01/2010 and 23/01/2010. Between those dates we should repeat or populate fake records with the value of 18/01/2010 (the latest one we had).
An even worse problem occurs AT THE END of the file cause I may have records for say GOOG stock code until today (26/01/2010) but I dont have records for the MICRO stock code. So I should REPEAT the latest record for MICRO from 23/01/2010 until 26/01/2010.
Got it ?
Really dont know what to do. Think some sort of FOR/NEXT looping accross the dates. But have no idea.
Thanks 4 your support !
BTW you have attached a simple plain text file where I illustrate the situation. Missing records are denoted by "###########". Those are the places where we must provide or populate a fake record.
Is it possible that you could enclose a file with an example (doesn't have to be the real data). I'm sure I have a simple solution for you but I always prefer to look first at the original data.
//First of all we must load the list of all codes (it must be done using DISTINCT method, so the codes won't be repeated)
Qualify *; //Qualify - means, that on ther begining of every field name QlikView will put the name of a table, in this case CODES. (see the result)
CODES:
LOADDISTINCT
ASSET_CODE
RESIDENT ASSETS;
MIN_MAX:
LOAD
Min(Num(Date(ASSET_DATE, 'DD/MM/YYYY'))) AS MIN, //Here we check what is the first date, when the asset was present (we must convert the date into number first)
Max(Num(Date(ASSET_DATE, 'DD/MM/YYYY'))) AS MAX //Here we check what is the last date, when the asset was present (we must convert the date into number first)
RESIDENT ASSETS;
Let min = PEEK('MIN_MAX.MIN', 0, MIN_MAX); //Here we download the data from table MIN_MAX
Let max = PEEK('MIN_MAX.MAX', 0, MIN_MAX); // ------//-----------------------------------
DROPTABLEMIN_MAX;
Unqualify *; // Unqualify - stops the adding the table name on the begining of each field name
//We must download the assets fro each ASSET_CODE for each possible ASSET_DATE (between First and Last ASSET_DATE)
Let a = $(min);
do
//Here we must check when was the last asset before certain date (variable $(a))
DATA_temp:
LOAD
ASSET_CODE AS ASSET_CODE_temp,
Max(Num(Date(ASSET_DATE, 'DD/MM/YYYY'))) AS ASSET_DATE_temp_1
RESIDENT ASSETS
WHERE
Num(Date(ASSET_DATE, 'DD/MM/YYYY')) <= $(a)
GROUPBY
ASSET_CODE;
LEFTJOIN
//When we have the last date, when the asset was present, then we can look for it in the table ASSETS
LOAD
ASSET_CODE AS ASSET_CODE_temp,
Num(Date(ASSET_DATE, 'DD/MM/YYYY')) AS ASSET_DATE_temp_1,
ASSET_VALUE AS ASSET_VALUE_temp
RESIDENT ASSETS
WHERE Num(Date(ASSET_DATE, 'DD/MM/YYYY')) <= $(a);
RIGHTJOIN
//Now we can can add the list of all ASSET_CODES
LOAD
CODES.ASSET_CODE AS ASSET_CODE_temp,
($(a)) AS ASSET_DATE_temp
RESIDENT CODES;
DATA:
LOAD
ASSET_CODE_temp AS ASSET_CODE_final,
Date(ASSET_DATE_temp, 'DD/MM/YYYY') AS ASSET_DATE_final,
If(IsNull(ASSET_VALUE_temp), 0, ASSET_VALUE_temp) AS ASSET_VALUE_final //If there was no asset on the beggining (for example you will have new ASSET CODE, which never existed before)
RESIDENT DATA_temp; //then the first ASSET_VALUE is zero (see ASSET_CODE no. 185)
DROPTABLEDATA_temp;
exitdowhen a = max
Let a=a+1;
loop
DROPTABLECODES;
// If you use the section below, you will choose only dates, which exist in table ASSETS, otherwise you will get all dates between First date and Last date (example 01/01/2010 which is not
// present in table ASSETS). But this is you decision
INNERJOIN
LOADDISTINCT
Date(ASSET_DATE, 'DD/MM/YYYY') AS ASSET_DATE_final
