topic Flag working days per project in QlikView

Flag working days per project

Tue, 08 Jul 2014 07:30:37 GMT

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!

Re: Flag working days per project

MK_QSL — Tue, 08 Jul 2014 08:20:52 GMT

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;

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

Re: Flag working days per project

Tue, 08 Jul 2014 08:42:04 GMT

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

Re: Flag working days per project

Tue, 08 Jul 2014 10:31:19 GMT

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;