Populating conversion rates for each unit

[#1]

Hi All,

I have  a table with unit, Rate, Date , however in the table I didn't have all the continues dates , so I need to generate continues dates for each unit. then the rate value has to assigned from the previous date. how do I do this, I have read a thread Generating Missing Data In Qlikview about the similar requirement but I didn't get the correct results, can any one help on this here I have an extra field unit  there I am getting issues. please find the sample data.

[Anonymous]

Could you share the qvw and describe what is not working properly in it ?

[#3]

thanks Bill Markham, Please find the attachment and my requirement in it.

[gsbeaton]

So do you mean that if you have data like this:

01/01/2016 | 1 | 4
04/01/2016 | 2 | 5

The data should expand out to this:

01/01/2016 | 1 | 4
02/01/2016 | 1 | 4
03/01/2016 | 1 | 4
04/01/2016 | 2 | 5

?

[#5]

yes correct Geotge Beaton

[gsbeaton]

Okay, you'll have to do this in a few steps then:

T1: //load test data
LOAD * Inline [
  Date, Unit, Rate
  01/01/2016, 1, 4  
  04/01/2016, 2, 5
  12/01/2016, 8, 7
  13/01/2016, 4, 3
  17/01/2016, 1, 8
];


T2:  //find the highest and lowest date values in the test data
  LOAD 
    max(FieldValue('Date', recno())) as maxDate,
    min(FieldValue('Date', recno())) as minDate
  AUTOGENERATE FieldValueCount('Date');


    LET vMaxDateID  = Num(Peek('maxDate', 0, 'T2'));
    LET vMinDateID  = Num(Peek('minDate', 0, 'T2'));

Drop Table T2;


AllDates:  //generate a temporary table with all dates
LOAD date($(vMinDateID)+IterNo()-1) AS Date
AUTOGENERATE (1) 
WHILE $(vMinDateID)+IterNo()-1<= $(vMaxDateID);


//Now join AllDates back into the original table:
Join AllDates:
LOAD 
 date(Date) as Date,
 Unit,
 Rate
 Resident T1
Order By Date Asc;


Drop Table T1;
 // Now reload the table, using the previous values where the table fields are empty

NoConcatenate
FinalData:
Load
 Date,
 if(isNull(Unit),Peek(Unit),Unit) as Unit,
 if(isNull(Rate),Peek(Rate),Rate) as Rate
Resident AllDates
Order by Date;

drop table AllDates;

Hopefully you can follow that code.  There may be a more succinct way of doing it, but this will run pretty quickly and should do what you need it to.

Let me know if you need any further explanation.

Cheers

George