Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rakeshkumar1890
Creator
Creator

Logical Condition with Longest Period

Hello everyone,

Need your help to find out this logical condition on script.

Perference:

  • First Preference on Category : ww supply issue
  • Second Preference on Category : forecast
  • Third Preference on Category : Others categories

1. Check Longest Period(difference max(Plandate)-min(Plandate) on CTS and Part. That Is MCP

2. Check Longest Period(difference max(Plandate)-min(Plandate) on CTS and Part and Category . That Is Rootcause.

3. if Longest MCP and Longest Period on Category are same then go to preference Category.  Example (CTS - 300)

4. if Longest MCP and Longest Period on Category are same then go to highest count of Solima(CTS -600)

Raw Data: Exclude (OutputReslut- Need to calculate on script)

CTSPartCategoryPlanDateSolimaOutputMaxPartOutputMaxCategoryOutputSolimaCount 
100P-123ww supply issue7/21/2020180066  
100P-123ww supply issue7/27/2020180066  
100L-123forecast7/21/2020190077 MCP
100L-123forecast7/28/2020190077 MCP
         
200M-123forecast9/1/2020150088 MCP
200M-123forecast9/9/2020150088 MCP
200O-123invalid reason code9/9/2020160000  
200P-123daily pull/jit process9/10/2020160000  
200Q-123strategic inventory9/10/2020170000  
         
300P-456forecast8/18/202030099 MCP
300P-456forecast8/27/202030099 MCP
300L-123strategic inventory8/18/202070099  
300L-123strategic inventory8/27/202070099  
         
400A-123forecast8/10/2020220017171 
400A-123forecast8/11/2020230017171 
400A-123forecast8/27/2020240017171 
400B-198ww supply issue8/10/202090017172MCP
400B-198ww supply issue8/11/202090017172MCP
400B-198ww supply issue8/27/202080017171 
         
500C-181strategic inventory8/1/2020290147  
500C-181strategic inventory8/2/2020291147  
500C-181strategic inventory8/8/2020292147  
500C-181daily pull/jit process8/3/20202931412 MCP
500C-181daily pull/jit process8/4/20202941412 MCP
500C-181daily pull/jit process8/5/20202971412 MCP
500C-181daily pull/jit process8/6/20202981412 MCP
500C-181daily pull/jit process8/15/20202991412 MCP
         
600D-367forecast8/1/2020340772MCP
600D-367forecast8/2/2020340772MCP
600D-367forecast8/8/2020341771 
600S-983forecast8/1/2020561771 
600S-983forecast8/2/2020281771 
600S-983forecast8/8/2020351771 
         
700D-367forecast8/1/2020234771MCP
700D-367forecast8/2/2020340771 
700D-367forecast8/8/2020341771 
700S-983forecast8/1/2020561771 
700S-983forecast8/2/2020281771 
700S-983forecast8/8/2020351771 

 

Output Result : 

CTSMCPRootCause
100L-123forecast
200M-123forecast
300P-456forecast
400B-198ww supply issue
500C-181daily pull/jit process
600D-367forecast

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@rakeshkumar1890  try below

 

Data:
LOAD CTS, 
     Part, 
     Category, 
     PlanDate, 
     Solima
FROM Table;

Left Join(Data)
LOAD CTS, 
     Part,
     max(PlanDate)-min(PlanDate) as [Longest MCP]
Resident Data
Group by CTS, 
     Part;

Left Join(Data)
LOAD CTS, 
     Part,
     Category,
     max(PlanDate)-min(PlanDate) as [Longest Time]
Resident Data
Group by CTS, 
     Part,
     Category;
     
Left Join(Data)
LOAD CTS, 
     Part,
     Category,
     Solima,
     Count(Solima) as cnt_solima
Resident Data
Group by CTS, 
     CTS, 
     Part,
     Category,
     Solima;
     
Left Join(Data)
LOAD CTS, 
     max([Longest MCP]) as Max_Longest_MCP,
     max([Longest Time]) as Max_Longest_Time
Resident Data
Group by CTS;

T1:
LOAD Distinct CTS, 
     Part as MCP, 
     Category as RootCause,
     lower(trim(Category)) as RootCause_key,
     cnt_solima
Resident Data
where(Max_Longest_MCP=[Longest MCP] and Max_Longest_Time=[Longest Time])
order by CTS,Part,PlanDate;

DROP Table Data;

Left Join(T1)
LOAD CTS,
     max(cnt_solima) as max_cnt_solima
Resident T1
Group by CTS;

Left Join(T1)
LOAD CTS,
     Concat(DISTINCT RootCause_key,'|') as All_RootCause_key
Resident T1
Group by CTS;

T2:
NoConcatenate
LOAD *,
     if(RowNo()=1,1,Peek(Num)+1) as Num,
    if(wildmatch(All_RootCause_key,'*ww supply issue*') ,'ww supply issue',
     if(wildmatch(All_RootCause_key,'*forecast*'),'forecast',All_RootCause_key)) as Preference_RootCause
Resident T1;

DROP Table T1;

Final:
LOAD CTS, 
     MCP, 
     Num,
     RootCause
Resident T2
where Preference_RootCause=RootCause_key and cnt_solima=max_cnt_solima;

DROP Table T2;

Inner Join(Final)
LOAD CTS,
     min(Num) as Num
Resident Final
Group by CTS;

DROP Field Num;

 

 

 

Screenshot 2020-10-17 100703.png

View solution in original post

8 Replies
Kushal_Chawda

@rakeshkumar1890  how logic for CTS -100 & 200 goes?

rakeshkumar1890
Creator
Creator
Author

@Kushal_Chawda Due to longest Period 7,8 for CTS -100 & 200

rakeshkumar1890
Creator
Creator
Author

I am not getting the  correct result as I posted above:

MST_B05_Temp:
LOAD CTS,Part,Category,Date#(PlanDate,'M/D/YYYY')as PlanDate, Solima INLINE [
CTS,Part,Category,PlanDate,Solima
100, P-123, ww supply issue, 7/21/2020, 1800
100, P-123, ww supply issue, 7/27/2020, 1800
100, L-123, forecast, 7/21/2020, 1900
100, L-123, forecast, 7/28/2020, 1900
200, M-123, forecast, 9/1/2020, 1500
200, M-123, forecast, 9/9/2020, 1500
200, O-123, invalid reason code, 9/9/2020, 1600
200, P-123, daily pull/jit process, 9/10/2020, 1600
200, Q-123, strategic inventory, 9/10/2020, 1700
300, P-456, forecast, 8/18/2020, 300
300, P-456, forecast, 8/27/2020, 300
300, L-123, strategic inventory, 8/18/2020, 700
300, L-123, strategic inventory, 8/27/2020, 700
400, A-123, forecast, 8/10/2020, 2200
400, A-123, forecast, 8/11/2020, 2300
400, A-123, forecast, 8/27/2020, 2400
400, B-198, ww supply issue, 8/10/2020, 900
400, B-198, ww supply issue, 8/11/2020, 900
400, B-198, ww supply issue, 8/27/2020, 800
500, C-181, strategic inventory, 8/1/2020, 290
500, C-181, strategic inventory, 8/2/2020, 291
500, C-181, strategic inventory, 8/8/2020, 292
500, C-181, daily pull/jit process, 8/3/2020, 293
500, C-181, daily pull/jit process, 8/4/2020, 294
500, C-181, daily pull/jit process, 8/5/2020, 297
500, C-181, daily pull/jit process, 8/6/2020, 298
500, C-181, daily pull/jit process, 8/15/2020, 299
600, D-367, forecast, 8/1/2020, 340
600, D-367, forecast, 8/2/2020, 340
600, D-367, forecast, 8/8/2020, 341
600, S-983, forecast, 8/1/2020, 561
600, S-983, forecast, 8/2/2020, 281
600, S-983, forecast, 8/8/2020, 351
700, D-367, forecast, 8/1/2020, 234
700, D-367, forecast, 8/2/2020, 340
700, D-367, forecast, 8/8/2020, 341
700, S-983, forecast, 8/1/2020, 561
700, S-983, forecast, 8/2/2020, 281
700, S-983, forecast, 8/8/2020, 351
];


LEFT JOIN(MST_B05_Temp)
Load
CTS,
Part,
Category,
min(PlanDate) as MWT05_Min_ReplanforCTS,
max(PlanDate) as MWT05_Max_ReplanforCTS,
max(PlanDate)-min(PlanDate) AS MWT05_CTS_MST_01
Resident MST_B05_Temp
Group BY CTS,Part,Category;

LEFT JOIN(MST_B05_Temp)
load CTS,
max(MWT05_CTS_MST_01) as MWT05_CTS_MST_MAX
Resident MST_B05_Temp
GROUP BY CTS;

MST_B05: /// Got data with Longest Period
NoConcatenate
Load *
Resident MST_B05_Temp
Where MWT05_CTS_MST_01 = MWT05_CTS_MST_MAX
;

Drop Table MST_B05_Temp;

left join
load CTS,concat(Category,'_') as chek1
resident MST_B05 group by CTS;

left join (MST_B05) /// Finding Highest Count of Solima
load CTS,Part,Category,Solima,
count(Solima) as Solimacount
resident MST_B05
group by CTS,Part,Category,Solima;

[SupplyIssueTmp]: // Checking Category for "ww supply issue"
noconcatenate
load *
resident MST_B05
where Match(Category,'ww supply issue')>0;

[MST_BRD05]: // Getting data for "ww supply issue" with highest count of solima
noconcatenate
load CTS,
FirstSortedValue(Part,-Solimacount) as Part,
FirstSortedValue(Category,-Solimacount) as Category,
FirstSortedValue(Solima,-Solimacount) as Solima
resident SupplyIssueTmp
group by CTS;

drop table SupplyIssueTmp;


[forecastTmp]: // Checking Category for "forecast"
noconcatenate
load *
resident MST_B05
where (Match(Category,'forecast')>0 and substringcount(chek1,'ww supply issue')=0);

[forecast]: // Getting data for "forecast" with highest count of solima
concatenate(MST_BRD05)
load CTS,
FirstSortedValue(Part,-Solimacount) as Part,
FirstSortedValue(Category,-Solimacount) as Category,
FirstSortedValue(Solima,-Solimacount) as Solima
resident forecastTmp
group by CTS;

drop table forecastTmp;

MST_B05_Final: // // Getting data for other Categories.
concatenate(MST_BRD05)
load *
resident MST_B05
where Match(chek1,'forecast','ww supply issue')=0;

drop table MST_B05;
drop fields chek1,Solimacount, PlanDate,MWT05_Min_ReplanforCTS,MWT05_Max_ReplanforCTS;

EXIT SCRIPT;

Kushal_Chawda

@rakeshkumar1890  how MCP of 700 is selected? Because for longest period for all same category solima is also unique. So what is the criteria to pick?

rakeshkumar1890
Creator
Creator
Author

@Kushal_Chawda for 700 it should be first row. whatever at loading time.

Kushal_Chawda

@rakeshkumar1890  try below

 

Data:
LOAD CTS, 
     Part, 
     Category, 
     PlanDate, 
     Solima
FROM Table;

Left Join(Data)
LOAD CTS, 
     Part,
     max(PlanDate)-min(PlanDate) as [Longest MCP]
Resident Data
Group by CTS, 
     Part;

Left Join(Data)
LOAD CTS, 
     Part,
     Category,
     max(PlanDate)-min(PlanDate) as [Longest Time]
Resident Data
Group by CTS, 
     Part,
     Category;
     
Left Join(Data)
LOAD CTS, 
     Part,
     Category,
     Solima,
     Count(Solima) as cnt_solima
Resident Data
Group by CTS, 
     CTS, 
     Part,
     Category,
     Solima;
     
Left Join(Data)
LOAD CTS, 
     max([Longest MCP]) as Max_Longest_MCP,
     max([Longest Time]) as Max_Longest_Time
Resident Data
Group by CTS;

T1:
LOAD Distinct CTS, 
     Part as MCP, 
     Category as RootCause,
     lower(trim(Category)) as RootCause_key,
     cnt_solima
Resident Data
where(Max_Longest_MCP=[Longest MCP] and Max_Longest_Time=[Longest Time])
order by CTS,Part,PlanDate;

DROP Table Data;

Left Join(T1)
LOAD CTS,
     max(cnt_solima) as max_cnt_solima
Resident T1
Group by CTS;

Left Join(T1)
LOAD CTS,
     Concat(DISTINCT RootCause_key,'|') as All_RootCause_key
Resident T1
Group by CTS;

T2:
NoConcatenate
LOAD *,
     if(RowNo()=1,1,Peek(Num)+1) as Num,
    if(wildmatch(All_RootCause_key,'*ww supply issue*') ,'ww supply issue',
     if(wildmatch(All_RootCause_key,'*forecast*'),'forecast',All_RootCause_key)) as Preference_RootCause
Resident T1;

DROP Table T1;

Final:
LOAD CTS, 
     MCP, 
     Num,
     RootCause
Resident T2
where Preference_RootCause=RootCause_key and cnt_solima=max_cnt_solima;

DROP Table T2;

Inner Join(Final)
LOAD CTS,
     min(Num) as Num
Resident Final
Group by CTS;

DROP Field Num;

 

 

 

Screenshot 2020-10-17 100703.png

rakeshkumar1890
Creator
Creator
Author

@Kushal_Chawda  Big Thanks Kush, Let me check on real data.

rakeshkumar1890
Creator
Creator
Author

Thank you so much @Kushal_Chawda