This is My Requirement :-
If there are 5 Weeks in the last month of the Quarter, consider 3 Weeks Quantity as “In Transit” and if there are 4 Weeks, consider 2 Weeks Quantity as “In Transit”.
and
this is script i written but i am not getting the OutPut, please help on this.
soi:
LOAD SupplyPN,
SupplySOI,
Supply,
SupplyWeekNo,
SupplyWeekDate,
Date(Left(SupplyWeekDate,8),'MM-DD-YYYY') as SupplyWeeksDate,
year( SupplyWeekDate) as Year,
month(SupplyWeekDate) as Month,
week(SupplyWeekDate) as Week,
'Q' & Ceil(month(SupplyWeekDate)/3) as quarters,
CoppSheetDate,
BU,
Commodity,
IsActive,
CreatedBy,
CreatedOn,
Id,
SupplierId
FROM
(qvd);
inner Join(soi)
LOAD
Round( Max(Ceil(Week))/2) as No_of_weeks,
Month
Resident soi group by Month;
Final_table:
NoConcatenate
LOAD *,
if(Week<=No_of_weeks,'In Transport') as Week_Quantity
Resident soi;
DROP Table soi;
