LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Hi Everyone - I'm looking to add a blank line between 2 sets of data that are being Concatenated before publishing to Excel so I don't have to manually open the Excel file and insert one before emailing out.

The two datasets are a Top 5 and Bottom 5 list - I want to put the break in to visually separate them.

To do this I've created a static data set with all 0s. I then use a Concatenate node to bring in the the first dataset, the 0s dataset, then the second dataset.

This works fine but of course I have a row of 0s where ideally I want Excel to have a blank line in the Output.

I realise this isn't exactly what Lavastorm is intended to do, but any tricky way round this ?

Thanks,

Dave

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Like you suggested, you need a row of nulls. If you create a static node with nulls and concatenate, then you'll get an empty row in Excel. If you'd like to do this dynamically, it gets a bit trickier. In the attached, I first extract the field name and type metadata. I then create a dummy table where one column is field names and the other is all nulls. I pivot to create one record of all nulls. If I were to concatenate at this point, I would get data type mismatches as all the field types are all strings. So I need to change the type to match that of the original. From there I can concatenate. Outputting to Excel show a blank line between the two blocks of data.
Attachments:
emptyLine.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Thanks as always Ryeh, the Metadata and Pivot table combo is a fantastic trick and will come in very handy elsewhere.

Just one question though - The simple answer of creating a static node with Nulls - This is what I couldn't do. I can create a row of the word "Null" or full stops or zeroes etc but not null cells so that it exports as blank.

How would you do this simple solution ?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Oh, right, I forgot that the static node is a little picky. The trick is to keep it uppercase, i.e. NULL. Copy and paste between the ---- lines into Lavastorm:

-------------------------------------

node:Static_Null
bretype:core::Static Data
editor:Label=Static Null
editor:sortkey=5459aa556caa507a_3
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
color:string, id:int,type:string,rand:int,junk:string
NULL,NULL,NULL,NULL,NULL
EOX
editor:XY=670,320
end:Static_Null

-------------------------------------

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Fantastic.

Worth also noting about the pickyness (as I've just spent 15 mins trying to work out) - you can't have any spaces in the NULL row or it errors
so no
NULL, NULL, NULL , NULL
It must be
NULL,NULL,NULL,NULL

Strange that it doesn't mind spaces if there are values in there, but for NULL it must have no spaces.


As feedback on Lavastorm as a whole - I've been told before that it's not really designed to be a report generating tool, but tricks like this combined with the Export to Template have saved me hours of report processing time and therefore considerable cost to business.

Thanks again.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Just for reference, if you wished to have actual empty fields, you could do this:
"",NULL,"",NULL,""

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Thanks - is there any difference between putting "" or Null ?