Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been given an excel file that is a glossary of kpi and measures used in the app.
I have not had to do this for over a year and I am totally blanking on this.
The spreadsheet has a first column called Term. The first row contains a value (Revenue). The next x rows fall under this value but the column is empty. Then you hit the next term (Invoice). Same scenario. Value on first row and the rest are blank.
I want to take that value and add it to each row in my data load. So if there are 7 rows then Revenue should appear in all 7 as the value for Term and so on.
I realize that I could just add the value to the spreadsheet. If it turns out that is easier then I just might do that. But I wanted to see my options. Thanks.
@rittermd wrote:I realize that I could just add the value to the spreadsheet. If it turns out that is easier then I just might do that. But I wanted to see my options. Thanks.
Yes, but as a definition, you should never alter the source data.
iamgie the excel have updates, so everytime you got a new file, you'll have to alter the file.
Hi @rittermd , try this with peek() function :
Data:
LOAD * INLINE [
Term, value
Revenue, 200
, 400
, 300
, 250
, 800
Invoice, 150
, 200
, 500
, 300
];
Load
Term,
if(rowno()=1, Term,if(isnull(Term) or Term='', peek('Term2'), Term)) as Term2,
value
Resident Data;
drop table Data;
@rittermd wrote:I realize that I could just add the value to the spreadsheet. If it turns out that is easier then I just might do that. But I wanted to see my options. Thanks.
Yes, but as a definition, you should never alter the source data.
iamgie the excel have updates, so everytime you got a new file, you'll have to alter the file.
Thanks. That gave me exactly what I needed.