Flag working days per project

[#1]

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!

[MK_QSL · Accepted solution]

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;

=====================================

[#3]

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

[#4]

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;