Help with a chart

[regowins]

Hi,

I have a table (Table 1) that lists managers with completion dates for jobs. I need to create a chart that lists out the managers that do not have a completion date as shown in the New Report Example.

Thanks for the help!!!

Table1
Gnum	Seq	Job	Mgr1	Date1	Mgr2	Date2	Mgr3	Date3
Hou	1	Fix1	Eric	7/1/13	Rus		John	8/1/13
Dal	4	Fix2	Josh		Peter		Steve	2/3/13
San	3	Fix3	Lance	6/1/12	Mike	5/1/13	Tom
New	Report	Example
Mgr	Gnum	Seq	Job
Rus	Hou	1	Fix1
Josh	Dal	4	Fix2
Peter	Dal	4	Fix2
Tom	San	3	Fix3

[jagan]

Hi Ashwin,

Try this script

Temp:

LOAD

*

INLINE [

Gnum, Seq, Job, Mgr1, Date1, Mgr2, Date2, Mgr3, Date3

Hou, 1, Fix1, Eric, 7/1/13, Rus,, John, 8/1/13

Dal, 4, Fix2, Josh,, Peter,, Steve, 2/3/13

San,3, Fix3, Lance, 6/1/12, Mike, 5/1/13, Tom, ];

Data:

LOAD

Gnum,

Seq,

Job,

Mgr1 AS Manager,

Date1 AS Date

RESIDENT Temp

WHERE Len(Date1) = 0;

Concatenate(Data)

LOAD

Gnum,

Seq,

Job,

Mgr2 AS Manager,

Date2 AS Date

RESIDENT Temp

WHERE Len(Date2) = 0;

Concatenate(Data)

LOAD

Gnum,

Seq,

Job,

Mgr3 AS Manager,

Date3 AS Date

RESIDENT Temp

WHERE Len(Date3) = 0;

DROP TABLE Temp;

Regards,

Jagan.

[regowins]

Thanks Jagan!  This was very helpful. I was thinking of something similar but not sure how to do it.   Any idea how I could also include the lead Mgr.  Basically the same rule as above but always show an extra line for the lead regardless if the date is blank or not. 

Lead	Gnum	Seq	Job	Mgr1	Date1	Mgr2	Date2	Mgr3	Date3
John	Hou	1	Fix1	Eric	7/1/13	Rus		John	8/1/13
Josh	Dal	4	Fix2	Josh		Peter		Steve	2/3/13
Tom	San	3	Fix3	Lance	6/1/12	Mike		Tom	5/1/13
New	Table
Mgr	Gnum	Seq	Desc
Rus	Hou	1	Fix1
John	Hou	1	Fix1
Josh	Dal	4	Fix2
Peter	Dal	4	Fix2
Mike	San	3	Fix3
Tom	San	3	Fix3

Thanks!

[jagan]

HI Ashwin,

Try this

Temp:

LOAD

*

INLINE [

Lead,Gnum, Seq, Job, Mgr1, Date1, Mgr2, Date2, Mgr3, Date3

John, Hou, 1, Fix1, Eric, 7/1/13, Rus,, John, 8/1/13

Josh, Dal, 4, Fix2, Josh,, Peter,, Steve, 2/3/13

Tom, San,3, Fix3, Lance, 6/1/12, Mike, 5/1/13, Tom, ];

Data:

LOAD

Lead,

Gnum,

Seq,

Job,

Mgr1 AS Manager,

Date1 AS Date

RESIDENT Temp

WHERE Len(Date1) = 0;

Concatenate(Data)

LOAD

Lead,

Gnum,

Seq,

Job,

Mgr2 AS Manager,

Date2 AS Date

RESIDENT Temp

WHERE Len(Date2) = 0;

Concatenate(Data)

LOAD

Lead,

Gnum,

Seq,

Job,

Mgr3 AS Manager,

Date3 AS Date

RESIDENT Temp

WHERE Len(Date3) = 0;

DROP TABLE Temp;

Regards,

Jagan.

[regowins]

Thanks Jagan!