concat

2 Topics

Aggregate Snap - CONCAT function - input contains nulls / undefineds
Judging from the options available in the Aggregate Snap, I would expect it to work much like the identically named aggregate functions in SQL. SQL functions like COUNT, AVG, SUM, and MAX conveniently skip over NULLs and return the same values as they would if the NULLs were not there. However, for CONCAT this does not appear to be the case. I am running into problems when the attribute I am trying to aggregate from the document stream either does not exist or is null. For example, if I had $original.foo as the input field in the document stream that looked something like (‘abc’, ‘xyz’, null, ‘aa’, undefined, ‘bb’) and was mapping CONCAT of that to an output field “bar”, I would expect bar to look like: “abc|xyz|aa|bb”. Instead I get errors: ‘foo was not found while evaluating the sub-expression “$original.foo”’ and ‘$original.original.LegacyKey is undefined. Perhaps you meant…’ How do I write the input field expression so nulls and undefineds are simply ignored? Or is this a bug in CONCAT / Aggregate? Thanks in advance! (PS: I don’t think I want a string with back-to-back delimiters, e.g. “abc|xyz||aa||bb” if I can avoid it, but even that would be better than failing on error.)
Solved
ForbinCSD
6 years ago Place Designing and Running Pipelines
3.6KViews
0likes
1Comment
Convert Excel Columns to JSON Arrays
Contributed by @hkaplan This pipeline reads in an Excel file with multiple columns, 1 to n number of rows with null cells and converts each Excel column into a JSON array with all of the null rows removed. Configuration The trick here is SnapLogic’s Aggregator Snap’s Concat function. Source: Excel spreadsheet Columnar data Target: Excel columnar data as an array Snaps used: File Reader, Excel Parser, Aggregate, Mapper, The sample Excel file looks like this: The resulting array looks like this: Downloads Convert Excel Columns to Arrays.slp (12.6 KB) ExcelColumnToArray.xlsx (8.7 KB)
hkaplan
8 years ago Place Patterns
8.6KViews
1like
0Comments