Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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!
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.
Thanks Jagan!