SnapLogic - Integration Nation

wahlborn · ‎08-28-2019

My goal is to execute a SQL query, return data, and format it so that it is accepted by a custom app we run here. So far, I have extracted the necessary data and formatted it using a mapper. The mapper output contains three columns that I need to move into a text file. For example:

ColumnA ColumnB ColumnC ColumnD ColumnE
DataA1 DataB1 DataC1 DataD1 DataE1
DataA2 DataB2 DataC2 DataD2 DataE2
DataA3 DataB3 DataC3 DataD3 DataE3

I need to output the data from the last three columns in the following format and create a text file:

DataC1
DataC2
DataC3
DataD1
DataD2
DataD3
DataE1
DataE2
DataE3

I’ve tried a handful of different snaps, and I haven’t succeeded in getting it to work. Anyone have tips on how to implement this?

cjhoward18 · ‎08-29-2019

@wahlborn

Here is a new slp which includes two new solutions to your problem, more optimized than the one given above earlier.

The solution using the pivot snap sorts by the column names, which might not result in the same order as coming in. It works now because the columns are C, D, E. But, if the last three columns in the real thing are not alphabetical, it might not work like you want.

The solution using the mapper snaps does not have the problem listed above. So, depending on your desired behavior you can choose and play with the solutions to fit your use case.

Also note, these solutions are using the actual column names themselves that are to be collected and written out, so if those plan to change this will need to be edited accordingly to handle that.

PivotQuestion_2019_08_29.slp (14.6 KB)

View solution in original post

Supratim · ‎08-29-2019

@wahlborn

The easiest way (but not professional way) to do this-
1.use copy snap and make three output view.
2. use one mapper with value of CloumnC and count var=1. (make use target value for all ColC,ColD,ColE should remain same. )
3. use one mapper with value of CloumnD and count var=2
4. use one mapper with value of CloumnE and count var=3
5. Use union to marge all 3 views.
6. Use sort snap by count variable.

wahlborn · ‎08-29-2019

Thank you! Can you explain further; I don’t know what you mean by implementing a count variable?

Supratim · ‎08-29-2019

TestData.slp (12.9 KB)

@wahlborn Please use the attached pipeline.

SnapLogic - Integration Nation

Assistance requested creating pipeline for SQL query-->mapper-->text file