Is it possible to count empty values in a column and replace with some values (i.e., linearly increasing values)
Can you do an ifelse and then do a running sum? The only thing you’d have to decide it how your want it sorted in increasing value.
runningSum(count(ifelse(isNull({field}) OR {field}=‘’,‘Count Me’,NULL)),[{sort_field} ASC])
1 Like
Hi @rkhan
Did you try out the solution provided by @Max ?
If it helped you resolve the question, please help the community by marking his answer as a “Solution.”
If we don’t hear back in the next 7 days, we will archive this question. Thanks!
No it didnot. I do not need sum. I want to count cells if less than and equals to (>=) 6 fill with increasing values i.e. if last value was 1, then 2, 3, 4. else delete empty cells
So do you want to count the empty cells or delete them?
If you want to delete them then you can filter them out and do a rank.
Can you explain more of what you are trying to accomplish, with screen shots of your dataset and what you want the end result to be?
thank you for the reply. I am new to QuickSight and seeking some guidance.I tried but I might not be doing right statement for the calculated field. I have hourly data which has many(dozens) nan values in many columns excel files. To upload on quick sight I replaced nan with empty cells(otherwise rows were skipped). Now I want to count empty cells (each cell represents hourly data point) if less than or equals to 12 hrs( or 12 empty cell) , replace the value. if greater than 12 then delete the cells.
for the last part you can use a filter that excludes anything above 12. What type of data is your hourly data column?
For your less than 12s… are you saying you’ll have data that’s like this:
1
2
3
empty
5
empty
empty
8
And you want it to be
1
2
3
4
5
6
7
8
?
In that case I would use a lag with an ifelse.
ifelse({hour_field}=‘’,lag({hour_field},[{hours_field} ASC])+1,{hour_field})
Now if there are multiple empty hours in a row you might need to do a nested if else.
ifelse({hour_field}=‘’,ifelse(lag({hour_field},[{hours_field} ASC])=‘’,lag({hour_field},[{hours_field} ASC])+2,2),lag({hour_field},[{hours_field} ASC])+1),{hour_field})
thank you! yes it is something like mentioned. i will be trying this.I just want to add one thing that I have scientific data (sensor data) so no missing time (hrs) but missing observed values. (note: I couldn’t share data due to privacy issues)
1 Like
Hi,I have a quick question. Can it be done in one calculated field count → if >= 12 → delete , else replace?
It is given me an error. It might be I am not writing it in correct way. Can you tell what is " ? is this inverted commas or for space ’ '? in both cases, it generates error that makes me confuse.
Also shows error to fix lag because lag should have 3 arguments not four
The’’ are something that come through on this forum. You should use regular quotes.
Can you show me your calculated field?
And in regards to the calculated field, a calculated field cannot delete a row. It can be renamed something, to which you then would filter out that value.
Hope that helps.
1 Like
I know calculated field always create a new col. or row. I tried to create calculated field using your line of code but couldn’t saved it. It was given error whenever I tried. I tried using ’ ’ but I was trying to create calculated field using int variable. Also Lag function also generate error even I tried some number. For example
ifelse({hour_field}= 0 ,lag({hour_field},[{hours_field} ASC])+1,{hour_field})
it also generates error.
What is the error it generates?
I mentioned in one of my reply (you can also see the replies) i.e., fix lag function because lag should have 3 arguments not four
Use this:
ifelse({hour_field}= 0 ,lag({hour_field},[{hours_field} ASC]+1,{hour_field})
You had an extra )
I exactly copied you. You first wrote:
ifelse({hour_field}=‘’,lag({hour_field},[{hours_field} ASC])+1,{hour_field})
Now you just gave the following that infact missed one ) [ also check your the most recent reply]
felse({hour_field}= 0 ,lag({hour_field},[{hours_field} ASC]+1,{hour_field})
I tried both and both lines generate errors
Can you mention what is the error. The is the exactly followed by the line you just gave:
I needed to add a ,1 to the lag function to have it look back one index. I also needed to make all of the calculations aggregations because a lag is.
Here is an example that works.
Replace arrival_timestamp with your HGM3.
ifelse(avg(extract(‘HH’,{arrival_timestamp}))=0,lag(avg(extract(‘HH’,{arrival_timestamp})),[extract(‘HH’,{arrival_timestamp}) ASC],1)+1,avg(extract(‘HH’,{arrival_timestamp})))
thanks Max. Before using your line of code, I want to confirm is HH you put here (seems string type) but in my case it is int data type. Should I convert the data type? [The constant issues I have experiencing because of the data types]
You can replace extract(‘HH’,{arrival_timestamp}) with HGM3.
My arrival_timestamp is a date that I needed to extract to a number.