你熟悉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查詢來完成
我會分幾個部分來做
- 通過摘要可以相當容易地獲得測定、質控批號和質控結果
- 隨著其他信息索引的變化，休息會變得更加困難（如果它們始終相同，這應該很可能是相當簡單的重新編碼和拆分）
- 根據分組（僅檢測？檢測+對照批號？），我將循環數據表並在使用關聯數組時添加值
代碼非常混亂（部分原因是無法使用 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;
);
