Tagged with replace-nulls-blanks - Visokio Forums

Idea: Variable column names and de-pivoting

ygulla — Fri, 02 Sep 2011 13:59:16 -0400

I've got the following challenge:

Input data is stored in a csv file, it has a daily changing number of columns (say 20-25), 10 of which have fixed names while the rest are named with dates. I want to de-pivot only the date-named columns.

The problem is that the number and names of the date columns change daily. Hence a simple de-pivot operation doesn't work once the data has been changed. Ideally I want a de-pivot option where it de-pivots all the columns apart from the selected ones.

Is it possible to achieve such de-pivoting in Omniscope some way? And if not would an option 'de-pivot everything apart from selected columns' be possible to include into de-pivot operation?

P.S. I tried every combination of operations I thought might lead me somewhere, and almost got the result, but in Summarise operation selecting "All fields" doesn't keep all the fields selected after the field names changed, the way that most other operations work. Is that intended?

Formulae: Aggregate rankings; Copying values from above

DBuzacott — Wed, 04 Dec 2013 20:02:28 -0500

Just wanted to share two methods I've got for seemingly common data problems. I don't claim that these are perfect (or even particularly efficient), but they do work. So I've detailed them here so that no-one else has to reinvent the wheel.

Power Tip 1) Aggregate rankings
I've got a file with the basic structure as follows:
[Name] [Value] and a whole lot of other fields

Aggregated rankings can be calculated as per this discussion (http://forums.visokio.com/discussion/comment/7339/). However, I wanted something that would display these rankings on non-aggregated data, as well as driving formulas for 'Top 10' and 'Top 100' categories (in this example, one of the KPIs is spend with top 100 Names).

To do this, I implemented the following workflow
Split off data
Delete excess fields (i.e. all but name / value)
Aggregate by Name
Rank by value (do not preserve formulas in output)
Merge rankings back into the original data
A screenshot of this is attached

Power Tip 2) Copying values from above
We have a dataset with the format
[Site]
A
null
null
B
null
null

i.e. a field that is only populated for the first row of each section of data.
There is some discussion of how to copy these values down into the null cells here (http://forums.visokio.com/discussion/comment/8577/#Comment_8577) but I'm not familiar with these scripts and so didn't want to use them.

Instead, I created some new formula fields as follows:
[Linenumber] = currentrow()
[Linenumber Site] = if([Site]=null, null, currentrow())
[Site updated] = CELL([Site], SUBSET_MAX([Linenumber Site], SUBSET([Linenumber], CURRENTROW(), "<=")))

This creates a new field [Site updated] with the values copied down. With the current dataset (19 fields, 10k records) these forumlas run pretty much instantly.
If you wanted to get sneaky, you could then rename [Site updated] to [Site] and delete the added fields. This would eliminate the calculations from the final file.

Changing values: Search/Replace blank with the left field before

nitiwan — Sat, 22 Oct 2011 03:00:01 -0400

Hi - Please advise how can use a Serch/Replace block to replace any blank field with a value from another field?
What formula I should put in the box "Replace with"? Thank you