need help with my script.

[Ayden]

hello all i need some help here with my script, I am trying to get  Start date and end date based on the Max GAP Month. 

Below is my script any help thanks all.

ColdStarts1:
LOAD
TEXT(PN) AS [Part Number],
PART_TYPE,
DESCRIPTION,
"PLANNED PN",
// LOCATION,
// AVGMOFCST,
"Inventory Qty",
DATE(NEXT_DELIV_DATE) AS NEXT_DELIV_DATE,
DATE(LAST_DELIV_DATE) AS LAST_DELIV_DATE ,
Year(DATE(NEXT_DELIV_DATE)) AS [Next Delivery Year],
// If (Isnull(NEXT_DELIV_DATE) and not isnull([Cold Start MRP Date]),Date(Today()),DATE(NEXT_DELIV_DATE)) as NEXT_DELIV_DATE,
SERV,
OVHL,
REPR,
NEW,
UNSERV,
((Year(NEXT_DELIV_DATE)*12) + Month(NEXT_DELIV_DATE)) - ((Year(LAST_DELIV_DATE)*12) + Month(LAST_DELIV_DATE)) AS [PROJECT GAP MONTHS],
// If(DATE(LAST_DELIV_DATE) < AddMonths(Today(),-24),'Cold','Warm') as [Risk Status],
DATE(LT_FULL_DATE) as LT_FULL_DATE,
LT_MONTHS,
// Applymap('Mapping_Cold_start',PN, Null()) as COLD_START_DATE,
Applymap('Mapping_Cold_start',Text(PN), Null()) as [Supply Plant] ,
IF(IsNull(F912_QTYRQD),NULL(),'Y') AS [MRP IN SMO2],
Applymap('Mapping_Cold_start_MOD_CNTR',PN, Null()) as MOD_CNTR,
// If([Ordering Plant Category]='Spares' and [Ordering Plant Category]='Engines','Spares',[Ordering Plant Category]) as [Ordering Plant Category],
If(Isnull([Ordering Plant Category Temp]),'Operations',
If([Ordering Plant Category Combined]='EnginesSpares','Spares',[Ordering Plant Category Temp])) as [Ordering Plant Category],
[Ordering Plant Category Combined],
// [Cold Start MRP Date],
[Cold Start Date],
Year([Cold Start Date]) as [Cold Start Year],
Month([Cold Start Date]) as [Cold Start Month],
// Year(Date([Cold Start MRP Date])) as [Cold Start MRP Year],
// creating Key to link this table with the link table in section 9.
Text(PN) &'|'& Applymap('Mapping_Cold_start',Text(PN), Null()) as Key ,
Date([Min Next Delivery Date]) as [Min Next Delivery Date]

FROM [lib://BADS Source QVDs/Transform/Fighter_Mobility/COLD_PARTS_TBL.qvd]
(qvd)

Where
Applymap('Mapping_Product_Line_Code',TEXT(PN)) <> 'DW'
AND Applymap('Mapping_Product_Line_Code',TEXT(PN)) <> 'TC'
AND NOT ISNULL( Applymap('Mapping_Cold_start',Text(PN), Null()));

Temp1:
Load *,
If([Part Number]=Previous([Part Number]) ,Peek(NEXT_DELIV_DATE),Date(Today())) as StartGap,
NEXT_DELIV_DATE as EndGap
;

Load
*
rESIDENT ColdStarts1
oRDER BY [Part Number],
//[Cold Start MRP Date]
NEXT_DELIV_DATE
;

dROP tABLE ColdStarts1;

//Calculating the MRP Gap Months
NoConcatenate
ColdStarts:
Load*,
((Year(EndGap)*12) + Month(EndGap)) - ((Year(StartGap)*12) + Month(StartGap)) as [MRP Gap Months]
rESIDENT Temp1
oRDER BY [Part Number],
//[Cold Start MRP Date]
NEXT_DELIV_DATE
;

Drop Table Temp1;

//Getting the Max Gap Months for a Part
Left JOin (ColdStarts)
LOad
[Part Number],
Max([MRP Gap Months]) as [Max MRP GAP Month]
Resident ColdStarts
Where [MRP Gap Months]>=0
Group By [Part Number];

[MeehyeOh]

Hi, @Ayden 

You mean like this?

-Source Data

- get  Start date and end date

[HeshamKhja1]

Can you please provide a sample data and the required outcome?

[Ayden]

Yes the goal is get the star and end dates based on the max MRP Gap Month. Eg Max MRP month is 1, so star should be 10/23 end should be 11/23 with out duplication. I tried getting min date and max dates out of the star gap and end gaps but the numbers were not what’s I need.

thanks

[HeshamKhja1]

Sorry Ayden I am still not getting it.

Are you trying to calculate the period between two consecutive dates? or you want for each part the period between max delivery date and min delivery date?

Assuming the sample data that @MeehyeOh provided as source is similar to yours, how do you want the result to be?

[MeehyeOh]

Hi, @Ayden 

I don't understand well. Can you provide the sample data?

And I have a question

1. Is MRP_GAP_MONTHS different per PN?

Eg PN1 MRP_GAP MONTHS is 1(10/23~11/23)
PN2 MRP_GAP_MONTHS is 2(9/20~11/20)

2. Or if you choose a cold year or a cold month, when do you want to see the start and end dates per PN that correspond to the selection conditions?

[Ayden]

1 yes that is correct each PN has a diffrent Gap dates 

2 it should correspond to the MAX MRP Gap Month. if the Gap is 1 PN 11233 start Gap 10/10/2023 end Gap 11/15/2023

Thanks

[MeehyeOh]

In response to @Ayden 

Like this...?

[Ayden]

Yes and thanks. 

[MeehyeOh · Accepted solution]

Hi @Ayden 

I used this script.

EXTRACT:

LOAD

    PartNum,

    Date(Date#(NEXT_DELIV_DATE,'YYYY.MM.DD'),'YYYY.MM.DD') As NEXT_DELIV_DATE,

    Date(Date#(LAST_DELIV_DATE,'YYYY.MM.DD'),'YYYY.MM.DD') As LAST_DELIV_DATE

FROM [lib://~~~/Data/Gap Month.xlsx]

(ooxml, embedded labels, table is Sheet1);

TRANSFORM:

Load

*,

((Year(EndGap)*12) + Month(EndGap)) - ((Year(StartGap)*12) + Month(StartGap)) as [MRP Gap Months]

;

Load 

*,

If([PartNum]=Previous([PartNum]) ,Peek(NEXT_DELIV_DATE),LAST_DELIV_DATE) as StartGap,

NEXT_DELIV_DATE as EndGap

;

Load

*

RESIDENT EXTRACT

ORDER BY [PartNum],NEXT_DELIV_DATE

;

DROP TABLE EXTRACT;

Reply thanks!