topic Re: numbers between in QlikView

numbers between

Sun, 29 Dec 2013 09:53:16 GMT

Hi All,

i have this data

LOAD * Inline [

Item,Date,Price

A,10/1/2010,100

A,10/2/2010,100

A,10/4/2010,100

A,10/7/2010,100

A,10/10/2010,150

A,10/11/2010,150

A,10/16/2010,150

];

as cleared above @ 10/10/2010 the price changed and become 150

if the price changed all dates above it their price will be the old one which is 100

i want to retrieve the dates between and their price

i.e i want the result to be

Item,Date,Price

A,10/1/2010,100

A,10/2/2010,100

A,10/3/2010,100

A,10/4/2010,100

A,10/5/2010,100

A,10/6/2010,100

A,10/7/2010,100

A,108/2010,100

A,10/9/2010,100

A,10/10/2010,150

A,10/11/2010,150

A,10/12/2010,150

A,10/13/2010,150

A,10/14/2010,150

A,10/15/2010,150

A,10/16/2010,150

how to do that?

Re: numbers between

maxgro — Sun, 29 Dec 2013 10:00:41 GMT

download this good pdf doc (Generating Missing Data In Qlikview) at

http://community.qlik.com/docs/DOC-3786

and goto pag. 7

Regards

Re: numbers between

qlikpahadi07 — Sun, 29 Dec 2013 11:36:09 GMT

Hi,

Find the attachment might be helpful

Re: numbers between

maxgro — Sun, 29 Dec 2013 20:51:56 GMT

Starting from the document above (http://community.qlik.com/docs/DOC-3786), in your case the code should be

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

TempTable:

LOAD * Inline [

Item,Date,Price

A,10/1/2010,100

A,10/2/2010,100

A,10/4/2010,100

A,10/7/2010,100

A,10/10/2010,150

A,10/11/2010,150

A,10/16/2010,150

];

MinMaxDate:

Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable;

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

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

Drop Table MinMaxDate;

Join (TempTable)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Table:

NoConcatenate Load

If( IsNull( Item ), Peek( Item ), Item ) as Item,

Date,

If( IsNull( Price ), Peek( Price ), Price ) as Price

Resident TempTable

Order By Date;

Drop Table TempTable;