Calculating Delay between 2 dates

[#1]

Hi All,

Please help me out in writing the expression or at script level.

I need to find the delay in no days.

Based on

1.1st Occurrence date of Forecast date and Last Occurrence date of Event Date and both should be Greater than Creation Date.

2. Decision should be Pass.

3. Also should calculate if Forecast Date of the Stage should be greater than previous pass Event Date

Example - While calculating Phase 2 Col 12 Forecast date is Greater than Col 9 Event date and it is passed at Phase 1 on 10/7/2014

S.no	ID	Name	Stage	Forecast Date	Event Date	Decision	Creation Date	Delay In No of Days
1	61	PV	Phase 1	9/12/2014			9/1/2014
2	61	PV	Phase 1	10/5/2014			9/1/2014
3	61	PV	Phase 1	10/9/2013			9/1/2014
4	61	PV	Phase 1	11/13/2014			9/1/2014
5	61	PV	Phase 1	10/3/2014			9/1/2014
6	61	PV	Phase 1		9/3/2014	Pass	9/1/2014
7	61	PV	Phase 1		9/3/2014	Cancelled	9/1/2014
8	61	PV	Phase 1		10/7/2014	Pass	9/1/2014
9	61	PV	Phase 1		10/7/2014	Pass	9/1/2014	25
10	61	PV	Phase 2	10/10/2014			9/1/2014
11	61	PV	Phase 2	10/5/2014			9/1/2014
12	61	PV	Phase 2	10/10/2014			9/1/2014
13	61	PV	Phase 2	10/8/2014			9/1/2014
14	61	PV	Phase 2	9/10/2014			9/1/2014
15	61	PV	Phase 2	10/9/2014			9/1/2014
16	61	PV	Phase 2		10/7/2014	Pass	9/1/2014
17	61	PV	Phase 2		11/11/2014	Pass	9/1/2014
18	61	PV	Phase 2		11/17/2014	Pass	9/1/2014	38

Thanks in advance.

Message was edited by: Vamsi P

[#2]

Thankyou anbu ,

Actually i edited the excel and posted here. in the application the fields are in different qvs source files,

how to load the specific fields into a single table we required from that different QVS files,

which method Resident load or apply map will work?

[anbu1984]

If you have key, value pair then use Apply map else join different QVS sources in script

[#4]

Hi Anbu,

I have a error while loading the data. Can u suggest me for the above code. which i executed today as the result is below.

/////////////////////////// Transformation 1

[SSG Slippage]:

Load

ProjectID_SYS,

[Event Type],

[Event Phase Review Name] as SSG_Event_Phase_Review_Name,

[Change-to Decision] as SSG_Change_to_Decision,

[Event Date],

If([Event Type] = 'Phase Review Date Change', [Event Type], Null()) as SSG_Event_Type ,

date([Change-to Date],'MM/DD/YYYY') as SSG_Forecast_Date,

if(wildmatch([Event Phase Review Name],'*Phase*'),date([Event Date],'MM/DD/YYYY')) as SSG_Event_Date

Resident [Project History];

Left join

LOAD

ProjectID_SYS,

[Project ID] as SSG_PRJ_ID,

[Project Creation Date] as SSG_PRJ_Creation_Date

Resident [Project Details];

/////////////////////////// Transformation 2

SSG_Slippage_1:

Load *,AutoNumber(RecNo(),SSG_PRJ_ID&SSG_Event_Phase_Review_Name) As RecNo

Resident [SSG Slippage];

Join (SSG_Slippage_1)

Load SSG_PRJ_ID,SSG_Event_Phase_Review_Name,Max(RecNo) As RecNo,If( FirstSortedValue(SSG_Forecast_Date,RecNo) > FirstSortedValue(SSG_PRJ_Creation_Date,RecNo)

And FirstSortedValue(SSG_Event_Date,-RecNo) > FirstSortedValue(SSG_PRJ_Creation_Date,RecNo)

And FirstSortedValue(SSG_Change_to_Decision,-RecNo) ='Pass',Interval(FirstSortedValue(SSG_Event_Date,-RecNo)-FirstSortedValue(SSG_Forecast_Date,RecNo),'d'))

Resident [SSG Slippage]

Group By SSG_PRJ_ID,SSG_Event_Phase_Review_Name;

Error showing as RecNo Not found

Thank you

[anbu1984]

RecNo is not present in table [SSG Slippage]. So you got that error.

Add this to table [SSG Slippage]

AutoNumber(RecNo(),SSG_PRJ_ID&SSG_Event_Phase_Review_Name) As RecNo

Why do you have two tables [SSG Slippage] and [SSG Slippage_1] with so many common fields? This will generate synthetic keys. You could have joined result of Firstsortedvalue() to table [SSG Slippage] directly like my script