topic Populating conversion rates for each unit in QlikView

Populating conversion rates for each unit

Thu, 04 Feb 2016 09:29:09 GMT

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.

Re: Populating conversion rates for each unit

Anonymous — Thu, 04 Feb 2016 09:32:57 GMT

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

Re: Populating conversion rates for each unit

Thu, 04 Feb 2016 09:51:14 GMT

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

Re: Populating conversion rates for each unit

gsbeaton — Thu, 04 Feb 2016 10:56:14 GMT

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

Re: Populating conversion rates for each unit

Thu, 04 Feb 2016 18:23:41 GMT

yes correct Geotge Beaton

Re: Populating conversion rates for each unit

gsbeaton — Mon, 08 Feb 2016 09:49:58 GMT

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