análisis resumido para datos dinámicosNecesito resumir un conjunto de datos complejo. El conjunto de datos contiene un montón de elementos que se procesaron en las últimas 2 semanas y cómo se desempeñaron. Para medir el rendimiento, tenemos calificaciones para diferentes categorías. Las categorías son de XZ001 a XZ100. También tengo sub categorías para cada categoría principal. Por ejemplo, XZ008 tiene subcategorías YZ801 a YZ809.
El conjunto de datos es dinámico, lo que significa que el conjunto de datos de 1 semana podría tener YZ 899, pero la otra semana podría no tenerlo.
Tengo ciertas categorías y subcategorías clasificadas en bucket1 y bucket2. Todas las demás categorías serían parte de bucket3.
A continuación se muestra el script que intenté escribir pero no parece funcionar.
Cualquier ayuda apreciada.
cdt = open("C:\Documents\Data\out.csv");
cdt=current data table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If( Contains( {"XZ008","XZ010","XZ015","XZ053","YZ1374","YZ1375","YZ9429","YZ9792","YZ9908", "YZ1930","YZ1933","YZ2744","YZ4621","YZ4625","YZ4630","YZ4640","YZ4681","YZ4830"}, colname ) |(colname >= "YZ901" & colname <= "YZ916") |(colname >= "YZ954" & colname <= "YZ965") |(colname >= "YZ1360" & colname <= "YZ1371") |(colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (colname >= "YZ3400" & colname <= "YZ3450"),
Insert Into( sum_lst2, col_lst2[i] )
); //Bucket1
If( Contains( {"XZ098","XZ090"}, colname ) | (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname <= "YZ9799"),
Insert Into( sum_lst4, col_lst2[i] )
);
);//Bucket2
If(!Contains( {"XZ008","XZ010","XZ015","XZ053","XZ098","XZ090","YZ1374","YZ1375","YZ9429","YZ9792","YZ9908", "YZ1930","YZ1933","YZ2744","YZ4621","YZ4625","YZ4630","YZ4640","YZ4681","YZ4830"}, colname ) |(colname >= "YZ901" & colname <= "YZ916") |(colname >= "YZ954" & colname <= "YZ965") |(colname >= "YZ1360" & colname <= "YZ1371") |(colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (colname >= "YZ3400" & colname <= "YZ3450")| (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname <= "YZ9799"),
Insert Into( sum_lst3, col_lst2[i] )
); //Bucket3
Eval( Eval Expr( cdt << New Column( "Bucket1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );
Conjunto de datos de muestra:
| Artículo |
YZ0100 |
YZ0801 |
YZ0805 |
YZ0806 |
YZ0807 |
YZ0813 |
YZ0814 |
YZ0815 |
YZ0817 |
YZ0871 |
YZ0874 |
YZ0881 |
YZ1367 |
YZ1374 |
YZ1541 |
YZ1571 |
YZ1573 |
YZ1575 |
YZ1577 |
YZ1583 |
YZ1585 |
YZ1590 |
YZ1591 |
YZ1594 |
YZ1933 |
YZ2701 |
YZ2708 |
YZ2737 |
YZ2740 |
YZ2744 |
YZ2745 |
YZ2749 |
YZ2750 |
YZ3404 |
YZ3407 |
YZ3415 |
YZ3416 |
YZ3417 |
YZ3418 |
YZ3422 |
YZ3423 |
YZ3492 |
YZ3493 |
YZ3502 |
YZ3534 |
YZ4621 |
YZ4625 |
YZ4821 |
YZ9031 |
YZ9429 |
YZ9901 |
YZ9908 |
XZ001 |
XZ008 |
XZ013 |
XZ015 |
XZ019 |
XZ027 |
XZ034 |
XZ035 |
XZ046 |
XZ048 |
XZ090 |
XZ094 |
XZ099 |
| AAA666 |
455 |
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
1 |
5 |
|
1 |
|
|
1 |
|
|
|
1 |
|
|
|
|
|
5 |
3 |
1 |
|
|
|
|
22 |
455 |
2 |
|
|
1 |
7 |
2 |
5 |
4 |
|
|
|
22 |
| AAA755 |
118 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
118 |
|
|
|
|
|
|
|
|
|
|
|
6 |
| AAA757 |
324 |
1 |
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
1 |
|
|
|
33 |
|
3 |
|
|
|
|
1 |
|
3 |
1 |
|
|
1 |
|
3 |
|
1 |
1 |
|
1 |
|
8 |
324 |
2 |
|
|
7 |
37 |
6 |
3 |
1 |
1 |
|
1 |
8 |
| BBA696 |
480 |
|
|
|
|
|
|
|
|
1 |
|
3 |
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
1 |
|
|
1 |
|
|
|
|
|
|
|
|
|
5 |
|
3 |
1 |
|
|
|
100 |
480 |
4 |
|
|
1 |
1 |
1 |
5 |
3 |
1 |
|
|
100 |
| BBA786 |
155 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
1 |
|
|
|
|
|
1 |
155 |
|
|
|
|
1 |
1 |
|
1 |
|
|
|
1 |
| BBA787 |
197 |
|
|
|
|
|
|
|
|
1 |
|
|
|
|
1 |
|
1 |
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
|
7 |
|
1 |
1 |
|
|
|
59 |
197 |
1 |
|
2 |
1 |
2 |
|
7 |
1 |
1 |
|
|
59 |
| CCA613 |
233 |
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
1 |
1 |
|
1 |
1 |
|
|
2 |
|
1 |
|
|
|
|
12 |
233 |
1 |
|
|
|
1 |
4 |
2 |
1 |
|
|
|
12 |
| CCA614 |
551 |
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
2 |
1 |
|
|
|
7 |
|
|
|
|
|
|
2 |
|
|
|
1 |
|
4 |
|
4 |
|
|
1 |
|
69 |
551 |
1 |
|
|
1 |
10 |
3 |
4 |
4 |
|
|
1 |
69 |
| CCA642 |
382 |
|
|
|
|
1 |
|
|
|
|
1 |
2 |
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
2 |
|
|
|
|
2 |
|
1 |
|
4 |
|
|
1 |
4 |
1 |
4 |
|
|
|
|
91 |
382 |
4 |
|
2 |
|
2 |
7 |
5 |
5 |
|
|
|
91 |
| CCA642R |
57 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
3 |
57 |
|
|
|
|
|
1 |
|
|
|
|
|
3 |
| CCB613 |
165 |
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
1 |
|
|
1 |
2 |
|
|
2 |
|
|
|
1 |
1 |
1 |
|
1 |
|
1 |
1 |
|
|
|
|
|
52 |
165 |
1 |
|
1 |
|
4 |
6 |
1 |
1 |
|
|
|
52 |
| DDA624 |
113 |
6 |
|
4 |
|
|
|
7 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
dieciséis |
113 |
19 |
|
|
|
1 |
1 |
|
|
|
|
|
dieciséis |
| DDA631 |
561 |
|
|
|
|
|
|
|
|
|
|
|
1 |
1 |
|
5 |
6 |
3 |
|
|
|
3 |
|
|
2 |
|
1 |
1 |
1 |
|
|
3 |
|
|
|
1 |
1 |
1 |
1 |
1 |
1 |
|
|
|
1 |
|
3 |
|
|
1 |
|
30 |
561 |
|
2 |
17 |
2 |
6 |
6 |
1 |
3 |
|
|
1 |
30 |
| DDA634 |
569 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
1 |
|
1 |
|
1 |
1 |
1 |
|
1 |
|
|
|
|
|
|
3 |
|
|
|
1 |
3 |
|
1 |
7 |
1 |
1 |
2 |
|
3 |
|
5 |
|
1 |
|
1 |
8 |
569 |
|
|
8 |
1 |
3 |
dieciséis |
3 |
5 |
|
1 |
|
9 |
| EEA624 |
228 |
|
1 |
1 |
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
1 |
2 |
2 |
1 |
|
|
|
|
|
|
|
15 |
2 |
54 |
228 |
3 |
|
|
|
3 |
6 |
|
|
|
|
15 |
56 |
| EEA627 |
495 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
12 |
15 |
7 |
|
|
|
3 |
|
|
|
|
1 |
|
|
|
|
1 |
2 |
2 |
1 |
1 |
|
1 |
3 |
5 |
2 |
1 |
|
|
7 |
|
3 |
|
1 |
2 |
|
28 |
495 |
|
|
37 |
|
4 |
dieciséis |
7 |
3 |
|
1 |
2 |
28 |
Tabla de datosRe: análisis resumido para datos dinámicosEl único error que encontré en su código es que su cierre ")" para su
For( i = 1, i <= N Items( col_lst2 ), i++,
se colocó después del Cubo 2, en lugar de después del Cubo 3.
Hice un par de modificaciones menores adicionales, pero el código funciona hasta donde puedo decir
Names Default To Here( 1 );
cdt = Open( "C:\Documents\Data\out.csv" );
Current Data Table( cdt );
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If(
Contains(
{"XZ008", "XZ010", "XZ015", "XZ053", "YZ1374", "YZ1375", "YZ9429", "YZ9792", "YZ9908", "YZ1930", "YZ1933", "YZ2744",
"YZ4621", "YZ4625", "YZ4630", "YZ4640", "YZ4681", "YZ4830"},
colname
) | (colname >= "YZ901" & colname <= "YZ916") | (colname >= "YZ954" & colname <= "YZ965") | (colname >= "YZ1360" &
colname <= "YZ1371") | (colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (
colname >= "YZ3400" & colname <= "YZ3450"),
Insert Into( sum_lst2, col_lst2[i] )
); //Bucket1
If(
Contains( {"XZ098", "XZ090"}, colname ) | (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname
<= "YZ9799"),
Insert Into( sum_lst4, col_lst2[i] )
);//Bucket2
If(
!Contains(
{"XZ008", "XZ010", "XZ015", "XZ053", "XZ098", "XZ090", "YZ1374", "YZ1375", "YZ9429", "YZ9792", "YZ9908", "YZ1930",
"YZ1933", "YZ2744", "YZ4621", "YZ4625", "YZ4630", "YZ4640", "YZ4681", "YZ4830"},
colname
) | (colname >= "YZ901" & colname <= "YZ916") | (colname >= "YZ954" & colname <= "YZ965") | (colname >= "YZ1360" &
colname <= "YZ1371") | (colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (
colname >= "YZ3400" & colname <= "YZ3450") | (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" &
colname <= "YZ9799"),
Insert Into( sum_lst3, col_lst2[i] )
); //Bucket3
);
Eval( Eval Expr( cdt << New Column( "Bucket1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );Re: análisis resumido para datos dinámicosDespués de cambiar la posición de la barra, mi código está funcionando, gracias. Pero los números en el cubo3 son incorrectos. Eso se debe a que los valores del cubo3 en la columna del artículo también se están agregando. ¿Cómo incluyo solo las columnas con el nombre de la columna que comienza como XZ e YZ? en el cubo 3?Re: análisis resumido para datos dinámicosNo entiendo el problema.Las siguientes columnas son las columnas que se suman para el depósito 3.
{YZ0100, YZ0801, YZ0805, YZ0806, YZ0807, YZ0813, YZ0814, YZ0815, YZ0817, YZ0871,
YZ0874, YZ0881, YZ1367, YZ1541, YZ1571, YZ1573, YZ1575, YZ1577, YZ1583, YZ1585,
YZ1590, YZ1591, YZ1594, YZ2701, YZ2708, YZ2737, YZ2740, YZ2745, YZ2749, YZ2750,
YZ3404, YZ3407, YZ3415, YZ3416, YZ3417, YZ3418, YZ3422, YZ3423, YZ3492, YZ3493,
YZ3502, YZ3534, YZ4821, YZ9031, YZ9901, XZ001, XZ013, XZ019, XZ027, XZ034, XZ035,
XZ046, XZ048, XZ094, XZ099}
¿Cuál de esas columnas no debería estar allí?
Re: análisis resumido para datos dinámicosTengo una declaración! Contains para bucket3. No es necesario agregar ninguna de estas columnas. Todas las columnas a partir de "XZ" e "YZ", excepto las agregadas en bucket1 y 2, deben agregarse para bucket3.Re: análisis resumido para datos dinámicosAh, dada esa lógica, que me perdí, creo que la forma en que debe expresarse es:
If(
Contains(
{"XZ008", "XZ010", "XZ015", "XZ053", "XZ098", "XZ090", "YZ1374", "YZ1375", "YZ9429", "YZ9792", "YZ9908", "YZ1930", "YZ1933", "YZ2744",
"YZ4621", "YZ4625", "YZ4630", "YZ4640", "YZ4681", "YZ4830"},
colname
) == 0,
If(
(colname >= "YZ901" & colname <= "YZ916") | (colname >= "YZ954" & colname <= "YZ965") | (colname >= "YZ1360" & colname <= "YZ1371") | (colname
>= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (colname >= "YZ3400" & colname <= "YZ3450") | (colname
>= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname <= "YZ9799"),
Insert Into( sum_lst3, col_lst2[i] )
)
); //Bucket3
