SUM times in dimension

[roberto99]

In the dimension I have this formula, But it is not correct, I need you to add all the different ones as well, without removing DISTINCT the formula does not work

My code:

trb_tmpProject:
LOAD Cod_Tie as ID, 
     'Tienda '&Cod_Tie as Name, 
     Cod_Tie&Dependienta as LineNo,
     entrada_horario as Start,
     salida_horario as End, 
     Dependienta as Resource
Resident Resultado;

TempIds:
LOAD
    Distinct ID&Resource as LineNo
Resident trb_tmpProject;


left join(trb_tmpProject)
Load
    LineNo,
    Floor(Rand() * 256) as R,
    Floor(Rand() * 256) as G,
    Floor(Rand() * 256) as B
Resident TempIds;


drop table TempIds;

_tmpProject:
LOAD ID as ID, 
     Name as Name, 
     Start as Start, 
     End as End, 
     RGB(R,G,B) as Color,  
     Resource as Resource, 
     RGB(R,G,B) as RGBColor
Resident trb_tmpProject;

drop table trb_tmpProject;



Project:
LOAD distinct
	 RowNo() as row,
	 ID, 
     Name, 
     Start as Start, 
     End as End,    
     timestamp(Start,'DD/MM/YYYY hh:mm:ss') as Entrada, 
     timestamp(End+maketime(0,1,0),'DD/MM/YYYY hh:mm:ss') as Salida,
     Interval(timestamp(End+maketime(0,1,0),'DD/MM/YYYY hh:mm:ss') - timestamp(Start,'DD/MM/YYYY hh:mm:ss'),'hh:mm') as ITime,
      Date(Floor( Start), 'DD/MM/YYYY') as Dia_trb,
     Resource,  
     RGBColor
resident _tmpProject where not isnull(Resource) or Resource<>'';



Totals:
load distinct
     Name,
     Resource,

     Dia_trb,
	 ITime as Working_Hr
resident Project
where Resource='ROCIO';



Left join(Project)
Load distinct
Name,
Resource,
Dia_trb,
time(sum(Working_Hr),'hh:mm') as Working_TIME
Resident Totals
group by Name, Resource, Dia_trb order by Dia_trb ASC;
drop table Totals;


DROP Table _tmpProject;

SET vColor = 'RGBColor';

Calendar code:

LET vMinDate = Num(MakeDate(2022,8,1));

LET vMaxDate = Num(MakeDate(2022,8,7));  //Num(Today());

CalendarTemp:

LOAD

  Date(Floor(DayStart(TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1))))) as DateID,

    TimeStamp($(vMinDate) + (RecNo()/4/24) + (IterNo() -1)) as DateTimeStamp    //TimeStamp($(vMinDate) + (RecNo()/60/24) + (IterNo() -1)) as DateTimeStamp

AUTOGENERATE 1439

WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));

Calendar:
LOAD  
    DateTimeStamp AS DateID,
    time(DateTimeStamp) as Horas,
    Day(DateTimeStamp) AS CalendarDay,
    WeekDay(DateTimeStamp) AS CalendarWeekDay,
    Week(DateTimeStamp) AS CalendarWeek,
    Month(DateTimeStamp) AS CalendarMonth,
    Year(DateTimeStamp) AS CalendarYear,
    'Q' & Ceil(Month(DateTimeStamp)/3) AS CalendarQuarter,
    WeekDay(DateTimeStamp) & '-' & Year(DateTimeStamp) AS CalendarWeekAndYear,
    Month(DateTimeStamp) & '-' & Year(DateTimeStamp) AS CalendarMonthAndYear
RESIDENT CalendarTemp where DateID>0 ORDER BY DateTimeStamp ASC;


IntervalMatch:
IntervalMatch(DateID)
Load
  Entrada, 
  Salida
Resident Project;

left join(Project)
load
	Entrada, 
	Salida,
	DateID
Resident IntervalMatch;

DROP TABLE IntervalMatch;
drop table CalendarTemp;

[JordyWegman]

Hi Roberto,

Could you add the QVW maybe? Then we can test this. Without data it's hard.

Jordy

Climber