for each group find out which value is missing in a specific column, then create a new row with the missing value for that group with 0 in the count column as a corresponding value for that row

j0rg — Fri, 06 Oct 2023 19:51:07 GMT

I have 4 columns in the mock data set (File attached below). The column "Companys" is grouped by column "Month-Year" and "State". Each "Companys" column value is associated to a value in column "Sectcor". I want find what values are missing in each group of Month-Year and State Column for example Sector "DK" has Companys "C" and "D" Associated to it but for group Jan-2021, CA we have 2 rows associated to it with Companys "C" and "D" for Sector "DK" but in group Feb-2021, CA there is only one row associated to it with Companys "D" for Setcor "DK". I want to insert the missing row for Companys "C" like Feb-2021, CA, "C" and 0 for the count column.

I want the row in red. Please help with this problem!

Re: for each group find out which value is missing in a specific column, then create a new row with the missing value for that group with 0 in the count column as a corresponding value for that row

hogi — Fri, 06 Oct 2023 20:54:53 GMT

You could split the data and then stack it again.

If a row is missing in the source table, there will be an empty spot in the split data table - and it will stay empty after stacking.

Therefore you have to replace the missing values with 0 at the end.

dt = New Table( "Sheet1",
	Add Rows( 16 ),
New Column( "Month - Year",
	Numeric,
	Format( "Format Pattern", "<Mmm>-<YYYY>"),
		Input Format( "Format Pattern", "<Mmm>-<YYYY>" ),
		Set Values(	{"Jan-2021", "Jan-2021", "Jan-2021", "Jan-2021", "Jan-2021", "Jan-2021","Jan-2021", "Feb-2021", "Feb-2021", "Feb-2021", "Feb-2021", "Feb-2021","Mrz-2021", "Mrz-2021", "Mrz-2021", "Mrz-2021"})),
	New Column( "State",Character,
		Set Values({"FL", "FL", "FL", "CA", "CA", "CA", "CA", "FL", "FL", "CA", "CA", "CA","FL", "FL", "FL", "FL"})),
	New Column( "Companys",	Character,Set Values({"A", "B", "C", "A", "C", "D", "E", "A", "B", "A", "B", "D", "A", "B","D", "E"})),
	New Column( "Sector",Character,	Set Values(	{"JF", "JF", "DK", "JF", "DK", "DK", "RT", "JF", "JF", "JF", "JF", "DK","JF", "JF", "DK", "RT"})),
	New Column( "Count",Set Values([2, 56, 4, 12, 17, 99, 4, 23, 20, 1, 15, 18,31, 2, 13, 10])));

dtSplit= dt << Split(
	Split By( :"Month - Year"n ),
	Split( :Count ),
	Group( :Sector, :Companys ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

dtStacked = dtSplit <<Stack(columns( :"Jan-2021"n, :"Feb-2021"n, :"Mrz-2021"n )
);

dtStacked:Data[Loc(IsMissing(dtStacked[0,"Data"]))]=0

topic Re: for each group find out which value is missing in a specific column, then create a new row with the missing value for that group with 0 in the count column as a corresponding value for that row in Discussions

for each group find out which value is missing in a specific column, then create a new row with the missing value for that group with 0 in the count column as a corresponding value for that row

Re: for each group find out which value is missing in a specific column, then create a new row with the missing value for that group with 0 in the count column as a corresponding value for that row