Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Grouper par semaine

Bonjour,

J'aimerais faire une moyenne de valeurs et les grouper par ID en ligne et par semaines en colonnes(sachant que j'ai un champ dates)

2 Solutions

Accepted Solutions

Bonjour,

Si j'ai bien compris, tu as besoin juste d'ajouter Semaine:

créer un tableau croisé avec colone Semaine, ligne ID et measure Avg(Valeur)

Table1:

LOAD *,week(Date#(DATE,'DD/MM/YYYY')) as Semaine INLINE [
ID, DATE, Valeur
1, 01/01/2020, 25
2, 02/01/2020, 12
3, 03/01/2020, 35
4, 07/03/2020, 12
5, 08/03/2020, 35
6, 09/03/2020, 25
7, 10/03/2020, 18
8, 21/06/2020, 26
9, 22/06/2020, 24
6, 23/06/2020, 28
7, 24/06/2020, 12
8, 25/06/2020, 35
9, 26/06/2020, 12
9, 12/08/2020, 35
6, 13/08/2020, 25
7, 14/08/2020, 31
];

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

avec For :

``````Data:
LOAD *,week(Date#(DATE,'DD/MM/YYYY')) as Semaine INLINE [
ID, DATE, Valeur
1, 01/01/2020, 25
2, 02/01/2020, 12
3, 03/01/2020, 35
4, 07/03/2020, 12
5, 08/03/2020, 35
6, 09/03/2020, 25
7, 10/03/2020, 18
8, 21/06/2020, 26
9, 22/06/2020, 24
6, 23/06/2020, 28
7, 24/06/2020, 12
8, 25/06/2020, 35
9, 26/06/2020, 12
9, 12/08/2020, 35
6, 13/08/2020, 25
7, 14/08/2020, 31
];

CombinedGenericTable:

output:

generic
load ID,Semaine,Avg(Valeur) as Moyenne resident Data group by ID,Semaine;

drop table Data;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName(\$(i));
//trace \$(i) - '\$(vTable)';
IF WildMatch('\$(vTable)', 'output.*') THEN

LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [\$(vTable)];

DROP TABLE [\$(vTable)];

ENDIF

NEXT i``````
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
4 Replies

Bonjour,

Si j'ai bien compris, tu as besoin juste d'ajouter Semaine:

créer un tableau croisé avec colone Semaine, ligne ID et measure Avg(Valeur)

Table1:

LOAD *,week(Date#(DATE,'DD/MM/YYYY')) as Semaine INLINE [
ID, DATE, Valeur
1, 01/01/2020, 25
2, 02/01/2020, 12
3, 03/01/2020, 35
4, 07/03/2020, 12
5, 08/03/2020, 35
6, 09/03/2020, 25
7, 10/03/2020, 18
8, 21/06/2020, 26
9, 22/06/2020, 24
6, 23/06/2020, 28
7, 24/06/2020, 12
8, 25/06/2020, 35
9, 26/06/2020, 12
9, 12/08/2020, 35
6, 13/08/2020, 25
7, 14/08/2020, 31
];

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor III
Author

Exactement oui

d'après ce que j'ai compris tu as besoin de ces colonnes en Script

dans ce cas il faut passer par generic load, par exemple :

``````Data:
LOAD *,week(Date#(DATE,'DD/MM/YYYY')) as Semaine INLINE [
ID, DATE, Valeur
1, 01/01/2020, 25
2, 02/01/2020, 12
3, 03/01/2020, 35
4, 07/03/2020, 12
5, 08/03/2020, 35
6, 09/03/2020, 25
7, 10/03/2020, 18
8, 21/06/2020, 26
9, 22/06/2020, 24
6, 23/06/2020, 28
7, 24/06/2020, 12
8, 25/06/2020, 35
9, 26/06/2020, 12
9, 12/08/2020, 35
6, 13/08/2020, 25
7, 14/08/2020, 31
];

output:

generic
load ID,Semaine,Avg(Valeur) as Moyenne resident Data group by ID,Semaine;

drop table Data;``````

output:

faire un for pour combiner les tables de Generic load

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

avec For :

``````Data:
LOAD *,week(Date#(DATE,'DD/MM/YYYY')) as Semaine INLINE [
ID, DATE, Valeur
1, 01/01/2020, 25
2, 02/01/2020, 12
3, 03/01/2020, 35
4, 07/03/2020, 12
5, 08/03/2020, 35
6, 09/03/2020, 25
7, 10/03/2020, 18
8, 21/06/2020, 26
9, 22/06/2020, 24
6, 23/06/2020, 28
7, 24/06/2020, 12
8, 25/06/2020, 35
9, 26/06/2020, 12
9, 12/08/2020, 35
6, 13/08/2020, 25
7, 14/08/2020, 31
];

CombinedGenericTable:

output:

generic
load ID,Semaine,Avg(Valeur) as Moyenne resident Data group by ID,Semaine;

drop table Data;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName(\$(i));
//trace \$(i) - '\$(vTable)';
IF WildMatch('\$(vTable)', 'output.*') THEN

LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [\$(vTable)];

DROP TABLE [\$(vTable)];

ENDIF

NEXT i``````
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉