Sind Sie mit SQL vertraut? Auf diese Weise können Sie das Problem vermeiden, dass Other Info unterschiedliche Indizes für verschiedene Schlüssel hat. Sie könnten eine Abfrage für die JMP-Tabelle erstellen, um sie in dieses Format zu bringen:
Erstellen Sie dann zusammenfassende und geteilte Tabellen und fügen Sie diese zusammen
Zusammenfassung:
Teilt:
Aufteilung aktualisiert zur Zusammenfassung:
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);
Bearbeiten:
Ich musste die Werte der ursprünglichen Datenspalten „Andere Informationen“ „Nützliche Informationen X“ in „Nützliche Informationen X“ (Kleinbuchstaben) umcodieren, damit die Abfrage funktioniert. Dies könnte auch in einer SQL-Abfrage erfolgen
Ich würde das in mehreren Teilen machen
- Mit der Zusammenfassung können Sie den Test, die Kontrollchargennummer und das Kontrollergebnis ganz einfach abrufen
- Es ist etwas schwieriger, sich auszuruhen, da sich die Indizes für andere Informationen ändern (wenn sie immer gleich wären, wäre das höchstwahrscheinlich ziemlich einfach: Neu kodieren und teilen).
- Abhängig von der Gruppierung (nur Test? Test- und Kontrollchargennummer?) würde ich die Datentabelle durchlaufen und Werte hinzufügen, während ich assoziative Arrays verwende
Der Code ist sehr chaotisch (teilweise weil ich For Each nicht verwenden kann und ich faul bin):
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;
);
