Need to fill empty row value with previous value in qliksense

[Rsaiq]

Hi All,

I have a sheet with some empty rows  in excel file just like below.

Expected output as below in qliksense

I need to fill missing values with previous value  if current row value is 0.

Could anyone please tell me how can i achieve this through qliksense script.

I have added raw data in a sheet and expected output in other sheet in a excel file.Kindly find attached excel file.

Thanks.

[Ivan_Bozov]

Try this in the script:

IF(Amount=0, PEEK(Amount),  Amount) AS Amount

[Rsaiq]

Hi @Ivan_Bozov ,

Thanks for your reply.

I tried with above line in the script but still didnt get the result....😥 .Getting same values in Amount & Amount2. Please refer below script and qliksense table.

Used below script

Output in the qliksense

Thanks

[NitinK7]

check the attached .QVF file

[Ivan_Bozov]

Well, now you introduced another field. 🙂

This is what I meant:

Data:
LOAD
    SCRIPT_ID,
    Type,
    Date,
    IF(Amount=0, PEEK(Amount), Amount) AS Amount
FROM ...
(ooxml, embedded labels, table is [Raw Data]);

[Rsaiq]

Hi @Ivan_Bozov @NitinK7 

Thanks guyzz ,Its working fine with above mentioned raw data .

But i need to do the same in other requiremets .I ahve a file where date are incomplete so i need to add missing dates first with master calender(I have added with the help of master calender) and then add value in missing dates but its not adding.

Can you please give any suggestions for this requirement.

I have added 2 sections in script editor i.e

Section 1-----Used raw data sheet which you provide the solutions(its working absolutely fine)

Section 2-----Created incomplete date raw sheet .
i) First Created the master calender script for adding missing date (its done),and

ii) Trying to add value in missing date with previous one.

Please find attched QVF file,Incomplete date file and complete date file.

Thanks for your co-operations

[NitinK7 · Accepted solution]

Hi,

please find the attched .QVF file might it is something you are looking for

[Rsaiq]

Thank you so much @NitinK7 ,it is what i was expecting.

Thanks once again...

[Rsaiq]

Hi @NitinK7 ,

I have created an XIRR calculations  on below data set.

XIRR is working fine but for only 1 script ID,if i am adding more than 1 script it is not working properly .

I am getting correct XIRR if i use only S750 0r S760 Script ID,together is not working properly.

For Example:

I am getting exactly below XIRR in qliksense on ScriptID S750.

Scenario 1 : XIRR   2.5%            Date Range : (From 19th March 2020 to 7th July 2021 )

Scenario 2 : XIRR  -7.8%           Date Range : (From 1st Nov 2020 to 31st Dec 2020 )

Scenario 3 : XIRR  -2.0%           Date Range : (From 1st Jan 2021 to 7th July 2021 )

I am getting exactly below XIRR in qliksense on ScriptID S760.

Scenario 1 : XIRR   2.1%            Date Range : (From 19th March 2020 to 7th July 2021 )

Scenario 2 : XIRR  -7.4%           Date Range : (From 1st Nov 2020 to 31st Dec 2020 )

Scenario 3 : XIRR  -3.8%           Date Range : (From 1st Jan 2021 to 7th July 2021 )

But when i am adding both ScriptID i.e S750 & S760 then its not working.

Could you please help me to resolve this issue .I tried this from different angels but didnt find any solutions.

I am attaching QVF file,XIRR output for Script ID S750 & S760.

Any help will be highly appreciated 

Thanks

[NitinK7 · Accepted solution]

please find the .QVF file, and try to calculate your XIRR  

I believe it will be correct now.