Hi I need to quickly fill in the empty collumn UTR (level 1& 2)Transaction Type (Subscription / Redemption)Total ValueNAV DateSub FundT0T1T2T3T4T5Level 1Redemption-10696254.2104-Jul-24UTR -TFO###### Redemption0 PruEmpire Redemption-401305.5333 UTR -TFO Redemption-2376941.413 UTR -TFO Subscription37291.57333 UTR -TFO Redemption-170306.7867 UTR -TFO Subscription9589.546667 UTR -TFO Subscription2903.706667 UTR -TFO Subscription66079.04 UTR -TFO Subscription44057.42667 UTR -TFO Subscription46087.42667 UTR -TFO Subscription28296.4 UTR -TFO into this UTR (level 1& 2)Transaction Type (Subscription / Redemption)Total ValueNAV DateSub FundT0T1T2T3T4T5Level 1Redemption-10696254.2104-Jul-24UTR -TFO######Level 1Redemption004-Jul-24PruEmpire######Level 1Redemption-401305.533304-Jul-24UTR -TFO######Level 1Redemption-2376941.41304-Jul-24UTR -TFO######Level 1Subscription37291.5733304-Jul-24UTR -TFO######Level 1Redemption-170306.786704-Jul-24UTR -TFO######Level 1Subscription9589.54666704-Jul-24UTR -TFO######Level 1Subscription2903.70666704-Jul-24UTR -TFO######Level 1Subscription66079.0404-Jul-24UTR -TFO######Level 1Subscription44057.4266704-Jul-24UTR -TFO######Level 1Subscription46087.4266704-Jul-24UTR -TFO######Level 1Subscription28296.404-Jul-24UTR -TFO###### Using multi-row formula works... but i have to set up for each column, is there a faster way? if isnull([UTR (level 1& 2)]) then [Row-1:UTR (level 1& 2)] else [UTR (level 1& 2)] endif

Fill in rows as per first row

Hi

I need to quickly fill in the empty collumn

UTR (level 1& 2)	Transaction Type (Subscription / Redemption)	Total Value	NAV Date	Sub Fund	T0	T1	T2	T3	T4	T5
Level 1	Redemption	-10696254.21	04-Jul-24	UTR -TFO	#	#	#	#	#	#
	Redemption	0		PruEmpire
	Redemption	-401305.5333		UTR -TFO
	Redemption	-2376941.413		UTR -TFO
	Subscription	37291.57333		UTR -TFO
	Redemption	-170306.7867		UTR -TFO
	Subscription	9589.546667		UTR -TFO
	Subscription	2903.706667		UTR -TFO
	Subscription	66079.04		UTR -TFO
	Subscription	44057.42667		UTR -TFO
	Subscription	46087.42667		UTR -TFO
	Subscription	28296.4		UTR -TFO

into this

UTR (level 1& 2)	Transaction Type (Subscription / Redemption)	Total Value	NAV Date	Sub Fund	T0	T1	T2	T3	T4	T5
Level 1	Redemption	-10696254.21	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Redemption	0	04-Jul-24	PruEmpire	#	#	#	#	#	#
Level 1	Redemption	-401305.5333	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Redemption	-2376941.413	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	37291.57333	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Redemption	-170306.7867	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	9589.546667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	2903.706667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	66079.04	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	44057.42667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	46087.42667	04-Jul-24	UTR -TFO	#	#	#	#	#	#
Level 1	Subscription	28296.4	04-Jul-24	UTR -TFO	#	#	#	#	#	#

Using multi-row formula works... but i have to set up for each column, is there a faster way?

if isnull([UTR (level 1& 2)])
then
[Row-1:UTR (level 1& 2)]

else [UTR (level 1& 2)] endif

Transformation

Accepted answers

Qiu

@Luqman
Combination of Transpose and CrossTab is good for multil column data processing.
I added some small tricks to restore the original field names and field order.

0704-Luqman.yxmd

Qiu

@Luqman
The Tile is used to retrieve the Column Order in the original data and then as you understand, the Dynamic Rename is used to change back to the oringal naming without changing the data itself.

All comments

Qiu

@Luqman
Combination of Transpose and CrossTab is good for multil column data processing.
I added some small tricks to restore the original field names and field order.

0704-Luqman.yxmd

Luqman

Hi @Qiu

Thankss, i got how you use transpose and tile to fill in the blanks

but can i get your help on the final bit.. ie to re-arrange back the columns as per the original data

the tool you use, 'dynamic rename' only renamed the Column name, without changing the data

Qiu

Luqman

@Qiu

Got it now.

I just needed to add a tool to remove 'record id' as this wasnt in the original data

thankss

Qiu

@Luqman
Good and you are welcome

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235