Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an qv app that gets data from two tables (simplified):
- Projects (ProjectID, Name)
- Hours (ProjectID, Date, bookedHours)
I'd like to autogenerate a third table in my script that list all the days between the first date with booked hours on a project until the last day. The records should contain a flag which says if there are booked hours on this day for this project.
Eg.
Projects:
ProjectID Name
1 Project1
2 Project2
Hours:
ProjectID Date Bookedhours
1 01/01/2014 6
1 01/03/2014 4
2 01/03/2014 3
2 01/06/2014 2
Autogenerated table should look this:
Workdays:
ProjectID Date Workday
1 01/01/2014 1
1 01/02/2014 0
1 01/03/2014 1
2 01/03/2014 1
2 01/04/2014 0
2 01/05/2014 0
2 01/06/2014 1
I have no clue how this can be done. Any help will be appreciated.
Thanks!
Please make sure that your set your Date format in the script editor as below...
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';
=========================================
Projects:
LOAD * Inline
[
ProjectID, Name
1, Project1
2, Project2
];
Hours:
Load * Inline
[
ProjectID,Date,Bookedhours
1,01/01/2014,6
1,01/03/2014,4
2,01/03/2014,3
2,01/06/2014,2
];
NoConcatenate
Temp1:
Load
ProjectID,
Date(Min(Date)) as MinDate,
Date(Max(Date)) as MaxDate
Resident Hours
Group By ProjectID;
Temp2:
Load
ProjectID,
Date(MinDate + IterNo() - 1) as Date
Resident Temp1
While MinDate + IterNo() - 1 <= MaxDate;
Join
Load * Resident Hours;
Final:
Load
ProjectID,
IF(IsNull(Bookedhours) or LEN(TRIM(Bookedhours))=0, 0,1) as Flag,
Bookedhours,
Date
Resident Temp2;
Drop Tables Temp1, Temp2, Hours;
=====================================
Please make sure that your set your Date format in the script editor as below...
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';
=========================================
Projects:
LOAD * Inline
[
ProjectID, Name
1, Project1
2, Project2
];
Hours:
Load * Inline
[
ProjectID,Date,Bookedhours
1,01/01/2014,6
1,01/03/2014,4
2,01/03/2014,3
2,01/06/2014,2
];
NoConcatenate
Temp1:
Load
ProjectID,
Date(Min(Date)) as MinDate,
Date(Max(Date)) as MaxDate
Resident Hours
Group By ProjectID;
Temp2:
Load
ProjectID,
Date(MinDate + IterNo() - 1) as Date
Resident Temp1
While MinDate + IterNo() - 1 <= MaxDate;
Join
Load * Resident Hours;
Final:
Load
ProjectID,
IF(IsNull(Bookedhours) or LEN(TRIM(Bookedhours))=0, 0,1) as Flag,
Bookedhours,
Date
Resident Temp2;
Drop Tables Temp1, Temp2, Hours;
=====================================
Hi Jan,
take a glance at my suggestion:
Hours:
LOAD * Inline [
ProjectID,Date, Bookedhours
1,01.01.2014,6
1,01.03.2014,4
2,01.03.2014,3
2,01.06.2014,2
];
Projects:
Left Join (Hours)
LOAD * Inline [
ProjectID,Name
1,Project1
2,Project2
];
// Now creating calender and Workdays:
Concatenate
LOAD
ProjectID,
0 AS Bookedhours,
1 AS WorkDay,
Name,
date(Date+ IterNo()) AS Date
Resident Hours
While not Exists(Date, Date+IterNo()) and Date < ('01.06.2014') // Maxdate hardcoded for timereasons, should be easy get it input
;
HtH
Roalnd
Thanks!
I did a modification to the join on the temp2 table and it worked:
Temp2:
Load
ProjectID,
Date(MinDate + IterNo() - 1) as Date
Resident Temp1
While MinDate + IterNo() - 1 <= MaxDate;
Left Join(Temp2)
Load
ProjectID,
Date,
Sum(Bookedhours) as Workinghours
Resident Hours
Group By Date, ProjectID;