cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
j0rg
j0rg
Level I

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

Oct 6, 2023 03:51 PM (855 views)

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!

Preview file
12 KB
0 Kudos
1 REPLY 1
hogi
hogi
Level XII

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

Oct 6, 2023 04:54 PM (844 views)  |  Posted in reply to message from j0rg 10-06-2023

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