Transpose columns(Dynamic) into rows.

[kumar25]

Hi,

I have to convert/transpose columns into rows. Basis the input file the count of columns may increase/decrease. Have to pick all the columns and convert them into rows.

Input:

INV111	INV112	INV113	INV114	INV115	INV116	INV117	INV118
TFR089	TFR2	TFR3	TFR34	TFR53	TFR67	TFR222	TFR902
TDR1	TDR087	TDR22	TDR44	TDR575	TDR58	TDR96	TDR67
TFET9	TFET7	TFET4	TFET22	TFET87	TFET34	TFET099	TFET123
VER0	VER1	VER2	VER3	VER4	VER5	VER6	VER7
USD	USD	USD	USD	USD	USD	USD	USD
FY24	FY24	FY24	FY24	FY24	FY24	FY24	FY24
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
4,206.21	46,557.34	5,701.96	5,775.38	18,677.77	6,046.35	4,733.21	4,764.49

Output:

INV111	TFR089	TDR1	TFET9	VER0	USD	FY24	Jan	4,206.21
INV112	TFR2	TDR087	TFET7	VER1	USD	FY24	Feb	46,557.34
INV113	TFR3	TDR22	TFET4	VER2	USD	FY24	Mar	5,701.96
INV114	TFR34	TDR44	TFET22	VER3	USD	FY24	Apr	5,775.38
INV115	TFR53	TDR575	TFET87	VER4	USD	FY24	May	18,677.77
INV116	TFR67	TDR58	TFET34	VER5	USD	FY24	Jun	6,046.35
INV117	TFR222	TDR96	TFET099	VER6	USD	FY24	Jul	4,733.21
INV118	TFR902	TDR67	TFET123	VER7	USD	FY24	Aug	4,764.49

[koryknick · Accepted solution]

@kumar25 - Here is another solution that does not use the Script snap.  I typically avoid the Script snap as it is notoriously difficult to debug and requires a different skill set that some developers may not yet possess.

This solution uses a Gate snap to gather all the data into a single array.  Please be conscious of how large the file is that you're consuming as this will load the entire dataset into memory, which can't be avoided in this case since you want to pivot the entire dataset.

Then in the Mapper, we're using the Object.keys() method to find the key names that were read in - basically just so we can loop through and grab all the values you want to pivot.  Then we're using the Array.map() method to change the value of each array element - this is where we're pivoting the records by re-creating the record from each set of column values.  Within the map() method, we're also using the Object.get() method to retrieve the value associated with the current field-name we're on.

After the Mapper, we have a JSON Splitter snap to pull the array elements into individual documents, completing the pivot.

Hope this helps!

[SpiroTaleski]

@kumar25 

The attached pipeline might help you achieve your objective.

BR,

Spiro

[kumar25]

Thanks @SpiroTaleski 

[koryknick · Accepted solution]

@kumar25 - Here is another solution that does not use the Script snap.  I typically avoid the Script snap as it is notoriously difficult to debug and requires a different skill set that some developers may not yet possess.

This solution uses a Gate snap to gather all the data into a single array.  Please be conscious of how large the file is that you're consuming as this will load the entire dataset into memory, which can't be avoided in this case since you want to pivot the entire dataset.

Then in the Mapper, we're using the Object.keys() method to find the key names that were read in - basically just so we can loop through and grab all the values you want to pivot.  Then we're using the Array.map() method to change the value of each array element - this is where we're pivoting the records by re-creating the record from each set of column values.  Within the map() method, we're also using the Object.get() method to retrieve the value associated with the current field-name we're on.

After the Mapper, we have a JSON Splitter snap to pull the array elements into individual documents, completing the pivot.

Hope this helps!

[kumar25]

Thanks @koryknick