SQL に精通していますか? こうすることで、キーごとに異なるインデックスを持つその他の情報の問題を回避できます。 JMP テーブルのクエリを作成して、次の形式に変換できます。
次に、要約テーブルを作成して分割し、それらを結合します。
まとめ：
スプリット：
分割を要約に更新:
Names Default To Here(1);
dt = Current Data Table();
dt_query = Query(
Table(dt, "INFO"),
"\[
select t1.assay, t1."Useful info 2" as "Control Lot number",
t1."Useful info 4" as "Control result", t1."Value for other info", t2."Value for other info"
from INFO t1
join INFO t2 on "Result for " || t1."Other info" = t2."Other info" and t1.assay = t2.assay
]\"
);
dt_summary = dt_query << Summary(
Group(:Assay, :Control Lot number, :Control result),
Freq("None"),
Weight("None"),
Link to original data table(0)
);
dt_split = dt_query << Split(
Split By(:Value for other info),
Split(:Value for other info 2),
Group(:Assay),
Remaining Columns(Drop All),
Sort by Column Property
);
dt_summary << Update(With(dt_split), Match Columns(:Assay = :Assay));
//Close(dt_summary, No save);
編集：
クエリを機能させるには、元のデータ列のその他の情報「有用な情報 X」の値を「有用な情報 X」（小文字）に再コードする必要がありました。 SQLクエリでも実行できます
これをいくつかの部分に分けて行います
- 分析、管理ロット番号、および管理結果の取得は、サマリーを使用して非常に簡単に行うことができます
- Other info インデックスが変化するため、残りを取得するのは少し難しくなります (インデックスが常に同じであれば、これはおそらく非常に単純な Recode と Split になるはずです)。
- グループ化（アッセイのみ？アッセイ + コントロールのロット番号？）に応じて、データテーブルをループし、連想配列を使用して値を追加します。
コードは非常に厄介です (For Each を使用できないことと、私が怠け者であることが部分的に原因です)。
Names Default To Here(1);
dt = New Table("Untitled",
Add Rows(22),
New Column("Assay", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3])),
New Column("Useful info 1",
Character(18),
"Nominal",
Set Values(
{"Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number",
"Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number",
"Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number",
"Control Lot number", "Control Lot number", "Control Lot number", "Control Lot number"}
)
),
New Column("Useful info 2",
Character(16),
"Nominal",
Set Values(
{"abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg",
"abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg", "abcdefg"}
)
),
New Column("Useful info 3",
Character(16),
"Nominal",
Set Values(
{"Control result", "Control result", "Control result", "Control result", "Control result", "Control result", "Control result",
"Control result", "Control result", "Control result", "Control result", "Control result", "Control result", "Control result",
"Control result", "Control result", "Control result", "Control result", "Control result", "Control result", "Control result",
"Control result"}
)
),
New Column("Useful info 4",
Numeric,
"Continuous",
Format("Best", 12),
Set Values([10, 10, 10, 10, 11, 11, 11, 11, 11, 11, 11, 11, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10])
),
New Column("Other info",
Character(24),
"Nominal",
Set Values(
{"Useful info 5", "Useful info 6", "Result for useful info 5", "Result for useful info 6", "Useful info 5", "Useful info 6",
"Useful info 7", "Useful info 8", "Result for useful info 5", "Result for useful info 6", "Result for useful info 7",
"Result for useful info 8", "Useful info 5", "Useful info 6", "Useful info 7", "Useful info 8", "Useful info 9",
"Result for useful info 5", "Result for useful info 6", "Result for useful info 7", "Result for useful info 8",
"Result for useful info 9"}
)
),
New Column("Value for other info",
Character(16),
"Nominal",
Set Values(
{"Method ID", "Method Version", "Method x", "1", "Method ID", "Method Version", "Sample result", "Equipment Number", "Method x", "1",
"12", "123456", "Method ID", "Method Version", "Sample result", "Equipment Type", "Equipment Number", "Method x", "1", "12", "Reader",
"123456"}
)
)
);
dt_result = dt << Summary(
Group(:Assay, :Useful info 2, :Useful info 4),
Freq("None"),
Weight("None"),
Link to original data table(0)
);
a_m = dt[0, "Assay"];
aa = Associative Array(a_m);
// will use slower for loop due to usage of JMP14 instead of For Each
aa_keys = aa << get keys;
For(i = 1, i <= N Items(aa_keys), i++,
// initialize associative array to aa
aa[aa_keys[i]] = Associative Array();
//Get rows for current assay
cur_rows = Loc(a_m, aa_keys[i]);
//
aa_temp = Associative Array(dt[cur_rows, "Other info"], dt[cur_rows, "Value for other info"]);
aa_temp_keys = aa_temp << get keys;
For(k = i, k <= N Items(aa_temp_keys), k++,
// if starts with Useful info -> this will be new key to aa[aa_keys]
If(Starts With(aa_temp_keys[k], "Useful info"),
aa[aa_keys[i]][aa_temp[aa_temp_keys[k]]] = aa_temp["Result for " || Lowercase(aa_temp_keys[k])]; // get value
);
);
);
//then loop over the AA to set values to dt_result. create new columns as you go
aa_keys = aa << get keys;
For(i = 1, i <= N Items(aa_keys), i++,
aa_new_cols = aa[aa_keys[i]];
cur_cols = Associative Array(dt_result << get column names("String"));
aa_new_cols << Remove(cur_cols);
new_cols = aa_new_cols << get keys;
For(k = 1, k <= N Items(new_cols), k++,
dt_result << New Column(new_cols[k], Character, Nominal);
);
dt_result[i, aa[aa_keys[i]] << get keys] = aa[aa_keys[i]] << get values;
);
