topic Re: Logical Condition in QlikView

Logical Condition

rakeshkumar1890 — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

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

Perference:

First Preference on Category : Supply Issue
Second Preference on Category : forecast
Third Preference on Category : Others categories

Check 1 :Per OrderLine on longestime, check category with given preference.

Check 2 :Per OrderLine on longestime, check category with given preference. If same category more than 1 then check into OrderNo counts. Maximum no. of counts should be output

Table:

OrderLine	Parts	LongestTime	Category	OrderNo.
CET0578	P24	13	supplier issue	304
CET0578	P24	13	forecast	305
CET0578	P05	18	Supply Issue	304
CET0578	P19	18	forecast	100

NIM0578	P24	13	supplier issue	304
NIM0578	P24	13	forecast	305
NIM0578	P24	18	supplier issue	304
NIM0578	P24	18	invalid reason code	246
NIM0578	P24	18	forecast	304

PCT0020	P18	10	supplier issue	685
PCT0020	P07	18	forecast	370
PCT0020	P41	18	supplier issue	938
PCT0020	P63	18	invalid reason code	938
PCT0020	P72	18	forecast	938
PCT0020	P92	18	forecast	370
PCT0020	P25	10	supplier issue	685
PCT0020	P10	18	Supply Issue	290
PCT0020	P14	18	supplier issue	564
PCT0020	P30	18	invalid reason code	564
PCT0020	P56	18	Supply Issue	564
PCT0020	P90	18	Supply Issue	290

PCT0950	P01	3	supplier issue	685
PCT0950	P03	2	supplier issue	938
PCT0950	P02	5	forecast	200
PCT0950	P04	5	invalid reason code	938
PCT0950	P05	5	forecast	938
PCT0950	P06	5	forecast	370

PCT0389	P01	3	Bcom	685
PCT0389	P03	4	supplier issue	938
PCT0389	P63	6	invalid reason code	938
PCT0389	P19	12	NA	938
PCT0389	P31	12	Maintenance	938
PCT0389	P22	12	Daily	370

Output Result: Logics

OrderLine	Parts	LongestTime	Category	OrderNo.	Remark
CET0578	P05	18	Supply Issue	304	Result was came due to first preference category - Supply Issue
NIM0578	P24	18	forecast	304	Result was came due to second preference category - forecast
PCT0020	P56	18	Supply Issue	564	Result was came due to more than 1 Supply Issue on each orderline PCT0020 at longest time 18, then I checked to Order No. 564 more than others on longest time ( I checked only "Supply Issue" not to forecast because Supply Issue is first preference
PCT0950	P05	5	forecast	938	Result was came due to more than 1 forecast on each orderline PCT0950 at longest time 5, then I checked to Order No. 564 more than others in forecast on longest time
PCT0389	P19	12	NA	938	Result was came due to third preference -- Checked Supply Issue or Forecast are there or not, If not then nothing to check.
PCT0389	P31	12	Maintenance	938
PCT0389	P22	12	Daily	370

Thanks to everyone for your input. I am waiting for the output result.

Please feel to reply if any doubts.

Kind Regards

Re: Logical Condition

Taoufiq_Zarra — Tue, 13 Oct 2020 08:19:46 GMT

@rakeshkumar1890 can you elaborate when we have more than one Supply Issue or forecast

PCT0020	P56	18	Supply Issue	564	Result was came due to more than 1 Supply Issue on each orderline PCT0020 at longest time 18, then I checked to Order No. 564 more than others on longest time ( I checked only "Supply Issue" not to forecast because Supply Issue is first preference
PCT0950	P05	5	forecast	938	Result was came due to more than 1 forecast on each orderline PCT0950 at longest time 5, then I checked to Order No. 564 more than others in forecast on longest time

and to be sure Supply Issue not supplier issue ?

Re: Logical Condition

rakeshkumar1890 — Tue, 13 Oct 2020 08:48:25 GMT

Thanks Taoufiq for your input.

Yes it may be more Supply Issue or forecast for each OrderLine, Parts and OrderNo.

Supply Issue and supplier issue are differ. These are two category

I want to more filter to raw data. Suppose there is no any longest time. Then in this case need logic on Category preference

Perference:

First Preference on Category : Supply Issue
Second Preference on Category : forecast
Third Preference on Category : Others categories

Check 1 :Per OrderLine, check category with given preference.

Check 2 :Per OrderLine, check category with given preference. If same category more than 1 then check into OrderNo counts. Maximum no. of counts should be output

We have the raw data like below:

OrderLine	Parts	Category	OrderNo
CET0578	P19	forecast	100
CET0578	P05	Supply Issue	304
NIM0578	P24	forecast	304
NIM0578	P24	invalid reason code	246
NIM0578	P24	supplier issue	304
PCT0020	P07	forecast	370
PCT0020	P92	forecast	370
PCT0020	P72	forecast	938
PCT0020	P30	invalid reason code	564
PCT0020	P63	invalid reason code	938
PCT0020	P14	supplier issue	564
PCT0020	P41	supplier issue	938
PCT0020	P10	Supply Issue	290
PCT0020	P90	Supply Issue	290
PCT0020	P56	Supply Issue	564
PCT0950	P02	forecast	200
PCT0950	P06	forecast	370
PCT0950	P05	forecast	938
PCT0950	P04	invalid reason code	938
PCT0389	P22	Daily	370
PCT0389	P31	Maintenance	938
PCT0389	P19	NA	938

Output result:

OrderLine	Parts	Category	OrderNo	Remark
CET0578	P05	Supply Issue	304	Result was came due to first preference category - Supply Issue
NIM0578	P24	forecast	304	Result was came due to second preference category - forecast
PCT0020	P56	Supply Issue	564	Result was came due to more than 1 Supply Issue on each orderline PCT0020, then I checked to Order No. 564 more than others ( I checked only "Supply Issue" not to forecast because Supply Issue is first preference
PCT0950	P05	forecast	938	Result was came due to more than 1 forecast on each orderline PCT0950, then I checked to Order No. 564 more than others in forecast.
PCT0389	P19	NA	938	Result was came due to third preference -- Checked Supply Issue or Forecast are there or not, If not then nothing to check.
PCT0389	P31	Maintenance	938
PCT0389	P22	Daily	370

Re: Logical Condition

rakeshkumar1890 — Tue, 13 Oct 2020 09:03:53 GMT

How we can set the logic--

1. First distinct OrderLine then check category for each part no. if found Supply Issue then check how many part no has this category if one time then stop the logic. if more than one time for each part no then we have to check OrderNo. in which highest count of orderno for supply issue.

2. Similarly for forcast.

3. If both(Supply Issue and forecast) are exist in same Orderline then first preference should be - Supply Issue then proceed with 1 above point.

4. if both are not exist then no logic further it should all partno and category.

Re: Logical Condition

Taoufiq_Zarra — Tue, 13 Oct 2020 09:41:00 GMT

@rakeshkumar1890 One option :

Input: LOAD * INLINE [ OrderLine, Parts, Category, OrderNo CET0578, P19, forecast, 100 CET0578, P05, Supply Issue, 304 NIM0578, P24, forecast, 304 NIM0578, P24, invalid reason code, 246 NIM0578, P24, supplier issue, 304 PCT0020, P07, forecast, 370 PCT0020, P92, forecast, 370 PCT0020, P72, forecast, 938 PCT0020, P30, invalid reason code, 564 PCT0020, P63, invalid reason code, 938 PCT0020, P14, supplier issue, 564 PCT0020, P41, supplier issue, 938 PCT0020, P10, Supply Issue, 290 PCT0020, P90, Supply Issue, 290 PCT0020, P56, Supply Issue, 564 PCT0950, P02, forecast, 200 PCT0950, P06, forecast, 370 PCT0950, P05, forecast, 938 PCT0950, P04, invalid reason code, 938 PCT0389, P22, Daily, 370 PCT0389, P31, Maintenance, 938 PCT0389, P19, NA, 938 ]; left join load OrderLine,concat(Category,'_') as chek1 resident Input group by OrderLine; [SupplyIssueTmp]: noconcatenate load * resident Input where Match(Category,'Supply Issue')>0 ; [SupplyIssue]: noconcatenate load OrderLine, FirstSortedValue(Parts,-OrderNo) as Parts, FirstSortedValue(Category,-OrderNo) as Category, max(OrderNo) as OrderNo resident SupplyIssueTmp group by OrderLine; drop table SupplyIssueTmp; [forecastTmp]: noconcatenate load * resident Input where (Match(Category,'forecast')>0 and substringcount(chek1,'Supply Issue')=0) ; [forecast]: concatenate(SupplyIssue) load OrderLine, FirstSortedValue(Parts,-OrderNo) as Parts, FirstSortedValue(Category,-OrderNo) as Category, max(OrderNo) as OrderNo resident forecastTmp group by OrderLine; drop table forecastTmp; output: concatenate(SupplyIssue) load * resident Input where wildMatch(chek1,'*forecast*','*Supply Issue*')=0; drop table Input; drop fields chek1;

output:

Re: Logical Condition

rakeshkumar1890 — Tue, 13 Oct 2020 09:53:26 GMT

Thanks you so much @Taoufiq_Zarra .

Let me check on my actual data.

Re: Logical Condition

rakeshkumar1890 — Tue, 13 Oct 2020 10:42:43 GMT

@Taoufiq_Zarra : As I checked script you are using Max(OrderNo).

Basically I don't want max(OrderNo). I need highest count of OrderNo, lets assume data,

Here 100 came 2 times. but in this 100 category preference is "forecast". resultant highlighted rows.

OrderLine	Parts	Category	OrderNo	Changed order
PCT0950	P02	forecast	200
PCT0950	P04	invalid reason code	100	Instead of 938 using 100
PCT0950	P05	forecast	100	Instead of 938 using 100
PCT0950	P06	forecast	370

Re: Logical Condition

Taoufiq_Zarra — Tue, 13 Oct 2020 11:10:45 GMT

@rakeshkumar1890 and like this ?

Input: LOAD * INLINE [ OrderLine, Parts, Category, OrderNo CET0578, P19, forecast, 100 CET0578, P05, Supply Issue, 304 NIM0578, P24, forecast, 304 NIM0578, P24, invalid reason code, 246 NIM0578, P24, supplier issue, 304 PCT0020, P07, forecast, 370 PCT0020, P92, forecast, 370 PCT0020, P72, forecast, 938 PCT0020, P30, invalid reason code, 564 PCT0020, P63, invalid reason code, 938 PCT0020, P14, supplier issue, 564 PCT0020, P41, supplier issue, 938 PCT0020, P10, Supply Issue, 290 PCT0020, P90, Supply Issue, 290 PCT0020, P56, Supply Issue, 564 PCT0389, P22, Daily, 370 PCT0389, P31, Maintenance, 938 PCT0389, P19, NA, 938 PCT0950, P02, forecast, 200 PCT0950, P04, invalid reason code, 100 PCT0950, P05, forecast, 100 PCT0950, P06, forecast, 370 ]; left join load OrderLine,concat(Category,'_') as chek1 resident Input group by OrderLine; left join (Input) load OrderLine,OrderNo,count(OrderNo) as countOrderNo resident Input group by OrderLine,OrderNo; [SupplyIssueTmp]: noconcatenate load * resident Input where Match(Category,'Supply Issue')>0 ; [SupplyIssue]: noconcatenate load OrderLine, FirstSortedValue(Parts,-countOrderNo) as Parts, FirstSortedValue(Category,-countOrderNo) as Category, FirstSortedValue(OrderNo,-countOrderNo) as OrderNo resident SupplyIssueTmp group by OrderLine; drop table SupplyIssueTmp; [forecastTmp]: noconcatenate load * resident Input where (Match(Category,'forecast')>0 and substringcount(chek1,'Supply Issue')=0) ; [forecast]: concatenate(SupplyIssue) load OrderLine, FirstSortedValue(Parts,-countOrderNo) as Parts, FirstSortedValue(Category,-countOrderNo) as Category, FirstSortedValue(OrderNo,-countOrderNo) as OrderNo resident forecastTmp group by OrderLine; drop table forecastTmp; output: concatenate(SupplyIssue) load * resident Input where wildMatch(chek1,'*forecast*','*Supply Issue*')=0; drop table Input; drop fields chek1,countOrderNo;

Re: Logical Condition

rakeshkumar1890 — Wed, 14 Oct 2020 07:30:09 GMT

Big thanks @Taoufiq_Zarra . I got the same solution