Copy prior months data to next month dynamically.

[SrinivasanSugumaran]

Hi Team, 

Can i seek your help on the below

I have a table which wil contain working dates for a month. I have data for few items which needs to be copied from prior month to all the dates of the month and this needs to happen dynamically. I tried using Join by Position when the data was for one month which worked. But when the data started to dynamically increase (table2)  i am unable to crack the code.

table1
		2023_04_03	2023_04_04	---	---	2023_04_25	2023_04_26	2023_04_27	2023_05_03	2023_05_04	---	---	2023_05_25	2023_05_26	2023_05_27
	grocery
	tv
	medicine
table2
		2023_03_31	2023_04_28
	grocery	400	100
	tv	450	150
	medicine	500	200
final output
		2023_04_03	2023_04_04	---	---	2023_04_25	2023_04_26	2023_04_27	2023_05_03	2023_05_04	---	---	2023_05_25	2023_05_26	2023_05_27
	grocery	400	400	400	400	400	400	400	100	100	##	##	100	100	100
	tv	450	450	450	450	450	450	450	150	150	##	##	150	150	150
	medicine	500	500	500	500	500	500	500	200	200	##	##	200	200	200

[PangHC]

1. transpose both data

2. join by type

3. filter out other date. (line in table 1 will removed if no data in table 2)

4. crosstab it back

[SrinivasanSugumaran]

Thanks for the solution. Really helpful.

A quick clarification.. will it be possible to bring the same output if i don't have the joining parameter on my table 1 .. 

i.e: in table 1 with all the dates of month suppose i have my first col. as blank ( currently it has grocery, tv , medicine)..in case if it does nt have any value and my table 2 has col 1 ( grocery tv medicine) will i be able to generate the same output.

[PangHC]

just add an union like this.

[SrinivasanSugumaran]

thanks for the revert.  But i dont think i will be able to join as type col wil b blank in Table 1 and in this case how do i bring my output 

table1
		2023_04_03	2023_04_04	---	---	2023_04_25	2023_04_26	2023_04_27	2023_05_03	2023_05_04	---	---	2023_05_25	2023_05_26	2023_05_27
table2
		2023_03_31	2023_04_28
	grocery	400	100
	tv	450	150
	meidcine	500	200
finla output
		2023_04_03	2023_04_04	---	---	2023_04_25	2023_04_26	2023_04_27	2023_05_03	2023_05_04	---	---	2023_05_25	2023_05_26	2023_05_27
	grocery	400	400	400	400	400	400	400	100	100	##	##	100	100	100
	tv	450	450	450	450	450	450	450	150	150	##	##	150	150	150
	medicine	500	500	500	500	500	500	500	200	200	##	##	200	200	200

[PangHC · Accepted solution]

then no choice to generate the date for the table 2 first. 

the input can be blank (or no line item), as second input will ensure at least 1 line item (ensure transpose have data, removed in the filter later).

unique to get the list of date, append + filter act like join before.

add select tool to forced the datatype as number and table structure

use join tool to remove duplicate (if there have type appear between table 1 and 2) then union. 

lastly, crosstab as previous.