Solved: Re: Logical Condition with Longest Period - Qlik Community

rakeshkumar1890 · ‎2020-10-15

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)

CTS	Part	Category	PlanDate	Solima	OutputMaxPart	OutputMaxCategory	OutputSolimaCount
100	P-123	ww supply issue	7/21/2020	1800	6	6
100	P-123	ww supply issue	7/27/2020	1800	6	6
100	L-123	forecast	7/21/2020	1900	7	7		MCP
100	L-123	forecast	7/28/2020	1900	7	7		MCP

200	M-123	forecast	9/1/2020	1500	8	8		MCP
200	M-123	forecast	9/9/2020	1500	8	8		MCP
200	O-123	invalid reason code	9/9/2020	1600	0	0
200	P-123	daily pull/jit process	9/10/2020	1600	0	0
200	Q-123	strategic inventory	9/10/2020	1700	0	0

300	P-456	forecast	8/18/2020	300	9	9		MCP
300	P-456	forecast	8/27/2020	300	9	9		MCP
300	L-123	strategic inventory	8/18/2020	700	9	9
300	L-123	strategic inventory	8/27/2020	700	9	9

400	A-123	forecast	8/10/2020	2200	17	17	1
400	A-123	forecast	8/11/2020	2300	17	17	1
400	A-123	forecast	8/27/2020	2400	17	17	1
400	B-198	ww supply issue	8/10/2020	900	17	17	2	MCP
400	B-198	ww supply issue	8/11/2020	900	17	17	2	MCP
400	B-198	ww supply issue	8/27/2020	800	17	17	1

500	C-181	strategic inventory	8/1/2020	290	14	7
500	C-181	strategic inventory	8/2/2020	291	14	7
500	C-181	strategic inventory	8/8/2020	292	14	7
500	C-181	daily pull/jit process	8/3/2020	293	14	12		MCP
500	C-181	daily pull/jit process	8/4/2020	294	14	12		MCP
500	C-181	daily pull/jit process	8/5/2020	297	14	12		MCP
500	C-181	daily pull/jit process	8/6/2020	298	14	12		MCP
500	C-181	daily pull/jit process	8/15/2020	299	14	12		MCP

600	D-367	forecast	8/1/2020	340	7	7	2	MCP
600	D-367	forecast	8/2/2020	340	7	7	2	MCP
600	D-367	forecast	8/8/2020	341	7	7	1
600	S-983	forecast	8/1/2020	561	7	7	1
600	S-983	forecast	8/2/2020	281	7	7	1
600	S-983	forecast	8/8/2020	351	7	7	1

700	D-367	forecast	8/1/2020	234	7	7	1	MCP
700	D-367	forecast	8/2/2020	340	7	7	1
700	D-367	forecast	8/8/2020	341	7	7	1
700	S-983	forecast	8/1/2020	561	7	7	1
700	S-983	forecast	8/2/2020	281	7	7	1
700	S-983	forecast	8/8/2020	351	7	7	1

Output Result :

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

Kushal_Chawda · ‎2020-10-17

@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;

View solution in original post

Kushal_Chawda · ‎2020-10-15

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

rakeshkumar1890 · ‎2020-10-16

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

rakeshkumar1890 · ‎2020-10-16

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 · ‎2020-10-16

@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 · ‎2020-10-17

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

Kushal_Chawda · ‎2020-10-17

@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;

rakeshkumar1890 · ‎2020-10-18

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

rakeshkumar1890 · ‎2020-10-18

Thank you so much @Kushal_Chawda.

Logical Condition with Longest Period

General Question