Modeling or set analysis

[Surya]

Dear All,

I have a table with Date and Rate

In that Table only working date data is available 

2nd Aug is Sunday Holiday so their is no data is available in Table in that case i need to show 

2nd Aug Rate is First Aug Rate

for example 1st Aug 1700 , 2nd aug no price 

2nd auguest = 1700

Help me out either modeling or Set analysis

[martinpohl]

first, load a table with all dates you want to

Temp:

load

'01/01/2020'+rowno()-1 as date

autogenerate (today()-'01/01/2020'+1);

then join your prices

left join

date,

price

from Yoursource;

Then clean your datas:

Data:

noconcatenate load

if(isnull(price),peek('price',-1),price) as price,

date

resident Temp;

drop table Temp;

[dplr-rn]

you can use

1- load script - previous function. https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFun...

2- in chart/table - above or below function

https://community.qlik.com/t5/QlikView-Documents/Missing-Manual-Above-and-Below/ta-p/1481948

in the script maybe the best solution. but need more details to be certain

[Kushal_Chawda]

Try below

Data:
load Date,
     Rate
FROM Source;

join(Data)
load date(MinDate+iterno()-1) as Date
while MinDate+iterno()-1<=MaxDate;
load min(Date) as MinDate,
     max(Date) as MaxDate
load date(fieldvalue('Date',recno())) as Date
autogenerate fieldvaluecount('Date');

Final:
noconcatenate
load Date,
     if(isnull(Rate),peek(Rate),Rate) as  Rate
resident Data
order by Date;

drop table Data;

[Surya · Accepted solution]

rate:

LOAD Date(DATE) as Date2,
[rate]
FROM path;

MinMaxDate:
Load Min(Date2) as MinDate, Today()-1 as MaxDate resident rate;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

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

Rate_main:
NoConcatenate Load Date2,
//Date(Date#(Date2,'DD/MM/YYYY'),'M/D/YYYY'),
If( IsNull([Rate]), Peek([Rate]), [Rate]) as [Rate]
Resident Rate
Order By Date2 ; /* so that above values can be propagated downwards */

Drop Table MinMaxDate, Rate;

[Kushal_Chawda]

@Surya  was my response was any different than yours?

[Surya]

Bhai. Initially developed Logic the same manner but I'm not getting exactly what I want. Later I'm using your logic.thats also not working in my scenario.

Thanks For your response.

[Kushal_Chawda]

But logic which you marked as correct and my response are identical, that's why I asked. Only the way you are generating is  Dates on resident load is different. Anyway, let's not debate and be happy as you got the answer that's what matters.