Date level values carry forward

[phaneendradodda]

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..

 

[Gysbert_Wassenaar]

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.

[ashok_rajm]

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;

[phaneendradodda]

Thank You for your reply...

[phaneendradodda]

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]

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]

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);