Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Rsaiq
Contributor III
Contributor III

Need to fill empty row value with previous value in qliksense

Hi All,

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

Rsaiq_0-1637656760088.png

Expected output as below in qliksense

Rsaiq_1-1637657356412.png

 

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.

Labels (5)
2 Solutions

Accepted Solutions
NitinK7
Specialist
Specialist

Hi,

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

View solution in original post

NitinK7
Specialist
Specialist

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

I believe it will be correct now.

 

View solution in original post

10 Replies
Ivan_Bozov
Luminary
Luminary

Try this in the script:

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

vizmind.eu
Rsaiq
Contributor III
Contributor III
Author

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

Rsaiq_0-1637665718559.png

Output in the qliksense

Rsaiq_1-1637665773149.png

Thanks

 

NitinK7
Specialist
Specialist

check the attached .QVF file

Ivan_Bozov
Luminary
Luminary

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

 

PEEK.PNG

vizmind.eu
Rsaiq
Contributor III
Contributor III
Author

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

Hi,

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

Rsaiq
Contributor III
Contributor III
Author

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

Thanks once again...

Rsaiq
Contributor III
Contributor III
Author

Hi @NitinK7 ,

I have created an XIRR calculations  on below data set.

Rsaiq_3-1638116104051.png

 

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 )

Rsaiq_0-1638114599957.png

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 )

Rsaiq_1-1638115931537.png

But when i am adding both ScriptID i.e S750S760 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
Specialist
Specialist

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

I believe it will be correct now.