topic Re: Logical Condition with Longest Period in QlikView

Logical Condition with Longest Period

rakeshkumar1890 — Fri, 15 Nov 2024 23:48:01 GMT

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

Re: Logical Condition with Longest Period

Kushal_Chawda — Thu, 15 Oct 2020 22:38:20 GMT

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

Re: Logical Condition with Longest Period

rakeshkumar1890 — Sat, 17 Oct 2020 08:20:17 GMT

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

Re: Logical Condition with Longest Period

rakeshkumar1890 — Fri, 16 Oct 2020 09:52:34 GMT

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;

Re: Logical Condition with Longest Period

Kushal_Chawda — Fri, 16 Oct 2020 16:12:32 GMT

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

Re: Logical Condition with Longest Period

rakeshkumar1890 — Sat, 17 Oct 2020 08:19:04 GMT

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

Re: Logical Condition with Longest Period

Kushal_Chawda — Sat, 17 Oct 2020 09:09:46 GMT

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

Re: Logical Condition with Longest Period

rakeshkumar1890 — Sun, 18 Oct 2020 08:03:36 GMT

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

Re: Logical Condition with Longest Period

rakeshkumar1890 — Sun, 18 Oct 2020 13:32:08 GMT

Thank you so much @Kushal_Chawda.