topic Re: Modeling or set analysis in QlikView

Modeling or set analysis

Surya — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Modeling or set analysis

martinpohl — Tue, 04 Aug 2020 13:35:13 GMT

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;

Re: Modeling or set analysis

dplr-rn — Tue, 04 Aug 2020 13:36:47 GMT

you can use

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

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

Re: Modeling or set analysis

Kushal_Chawda — Tue, 04 Aug 2020 13:45:41 GMT

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;

Re: Modeling or set analysis

Surya — Thu, 13 Aug 2020 06:45:51 GMT

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;

Re: Modeling or set analysis

Kushal_Chawda — Thu, 13 Aug 2020 09:37:29 GMT

@Surya was my response was any different than yours?

Re: Modeling or set analysis

Surya — Wed, 19 Aug 2020 11:38:20 GMT

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.

Re: Modeling or set analysis

Kushal_Chawda — Wed, 19 Aug 2020 11:46:56 GMT

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.