Skip to Content
0
Jun 28, 2022 at 02:24 PM

Merging Tables from 2 Queries

60 Views

Hello,


I would like to merge two tables that are placed in two reports.

Table A contains data like this:

image.png

UF A, GBCP A and Nature are Dimensions from query One.

Jan to Déc, are variables (but dimensions too, not sure if it should be tho.) as well. Each variable calculates the given number using two dates (begin, end) that can overlap over days, weeks or months.

In bold, the (Sorry for French, double Sorry i haven't made these variables) source code of the variable for January, it's similar for every month (So x12 variables) ; in italic, the explanation.

  • = Si (NuméroMoisAnnée([Date de fin de l'absence1])<1) Ou (NuméroMoisAnnée([Date de début de l'absence1])>1) Alors 0
  • If first day of sick leave is outside January, then 0
  • Sinon Si (NuméroMoisAnnée([Date de début de l'absence1])<1) Et (NuméroMoisAnnée([Date de fin de l'absence1])>1) Alors 31
  • If it's the complete month of January, then 31
  • Sinon Si (NuméroMoisAnnée([Date de début de l'absence1])=1) Et (NuméroMoisAnnée([Date de fin de l'absence1])=1) Alors (JoursEntre([Date de début de l'absence1];[Date de fin de l'absence1])+1)
  • If it's in January but not a complete month, then DaysBetween both dates
  • Sinon Si (NuméroMoisAnnée([Date de début de l'absence1])=1) Alors (JoursEntre ([Date de début de l'absence1]; DernierJourDuMois([Date de début de l'absence1]))+1)
  • This is kinda weird, it does the same as the previous line, but DaysBetween the beginning of the sick leave, and end of january.
  • Sinon Si (NuméroMoisAnnée([Date de fin de l'absence1])=1) Alors (NuméroJourDuMois([Date de fin de l'absence1]))
  • And again, if the month if January, then 31.
The number shown in the table is actually the sum of sick days of every worker in the given month, Nature, GBCP & UF. (For year 2022)

Table B contains data like this:

image.png

UF B, GBCP B, Year and Month B are Dimensions from query Two. (Note: UF A and UF B are linked dimensions, so does GBCP.)

Nb Agents and Working Days are variables stored as indicators.


What I'm looking for:

  • I want to add the Nature and the total of sick days inside my table B so I would have for each line, UF, GBCP, Year, Month, Nb Agents, Working Days, Nature, and Sick days total.

Any idea how that would be possible ?

Thanks,

Nicolas

Attachments

image.png (27.2 kB)
image.png (26.5 kB)