Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jboy
Contributor
Contributor

Urgent: Calling out Only the most recent value greater than 0 for each row

Dear All,

Please help!

 

I have a table containing different items in a row and each Items have values for 3 Periods. I want to create a column that gives only the most recent value that is greater than 0.

 

just as an example, if Period A = 0, then show value for Period B, if Period B = 0 and Period A = 0 then show value for Period C.

 

Thanks in Advance

6 Replies
nisha_rai
Creator II
Creator II

Hi,

Can you share the sample data.

 

sagarjagga
Creator
Creator

You can use RangeMax Function, Like-

RangeMax(Period A Value, Period B Value, Period C value).

 

jboy
Contributor
Contributor
Author

 

As you can see below, the data in the column (Most Recent Cost (€) contain the most recent cost across the four periods.

Please assist.

ItemMar-19 Cost (€)Apr-19 (€)Sept-19 (€)Mar-20 (€)Most recent Cost (€)
A00130034000340
A0020180300325325
A003002500250
A0040560590595595
A005003300330
A0067300745750750
jboy
Contributor
Contributor
Author

Thank you. The rangemax function does not apply here because the most recent balue may not necessarily be the highest value.

sagarjagga
Creator
Creator

if your number of columns are not giving to be changed in qliksense they you can use like

If(Column(4)>0,column(4),if(column(3)>0,Column(3),if(column(2)>0,column(2),column(1)))).

This is not the best solution. in best way , you can use crosstable in data model and use the first sorted non zero value.

 

nisha_rai
Creator II
Creator II

Hi,

Try the below code, may it helps to resolve your issue.


MR1:
CrossTable (MRDate,Value,1)
Load * Inline [
Item, 3/1/2019, 4/1/2019 , 9/1/2019, 3/1/2020
A001, 300 ,340, 0 ,0
A002, 0, 180, 300, 325
A003, 0, 0, 250, 0
A004, 0, 560 ,590, 595
A005, 0, 0, 330, 0
A006, 730 ,0 ,745, 750
];

//NoConcatenate
MR:
Load Item,

LastValue(Value) as MostRecent
Resident MR1
where Value<>0
group by Item
;

//Drop Table MR1;

exit Script;MR.PNG