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_032023_04_04------2023_04_252023_04_262023_04_272023_05_032023_05_04------2023_05_252023_05_262023_05_27 grocery tv medicine table2 2023_03_312023_04_28 grocery 400100 tv450150 medicine500200 final output 2023_04_032023_04_04------2023_04_252023_04_262023_04_272023_05_032023_05_04------2023_05_252023_05_262023_05_27 grocery400400400400400400400100100####100100100 tv450450450450450450450150150####150150150 medicine500500500500500500500200200####200200200

Copy prior months data to next month dynamically.

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

Alteryx Practice

Accepted answers

AncientPandaman

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.

Screenshot 2023-09-26 112208.png

example.yxmd

All comments

AncientPandaman

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

Screenshot 2023-09-25 122510.png

example.yxmd

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.

AncientPandaman

just add an union like this.

Screenshot 2023-09-25 133927.png

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

AncientPandaman

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.

Screenshot 2023-09-26 112208.png

example.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335