Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
phaneendradodda
New Contributor

Date level values carry forward

hello all...

I am new to Qlik. My issue is carry forward values, but i want in date level.  Can we hold the first generate  score (03-12-18) and second generate score (07-12-18) Dates. Based on these two dates can we get the new score ?  Plz see the image below.

Thanks and Regards..

 1.PNG

 

6 Replies
MVP & Luminary
MVP & Luminary

Re: Date level values carry forward

You can find the solution in this document: https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

And yes, this works in Qlik Sense too.


talk is cheap, supply exceeds demand
ashok_rajm
New Contributor III

Re: Date level values carry forward

Use below syntax to create new field (if Your Score is =0  or Score isnull then your Score will be carry forwarded).

LOAD
"Date",
Score,
if(len(trim(Score))=0 or Score=0,Peek(New_score),Score) as New_score
resident DATA;

 

Dates.PNG

phaneendradodda
New Contributor

Re: Date level values carry forward

Thank You for your reply...

phaneendradodda
New Contributor

Re: Date level values carry forward

Thank you Ashok

 If some dates are missing means how can we calculate.... i.e for example  : 3/12/18--------- 45, 7/12/18------- 75.

how can we get new score for the dates 4/12/18 to 6/12/18  as 45.

ashok_rajm
New Contributor III

Re: Date level values carry forward

To get missing dates you need to create Master Calendar. Below script to create master Calendar. Once Calendar is created you can join both the tables with Date field and get the new score.
ashok_rajm
New Contributor III

Re: Date level values carry forward

Refer: https://community.qlik.com/t5/QlikView-Scripting/Creating-A-Master-Calendar/td-p/341286

Temp:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident Data;


Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as NewDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);