Logical Condition

[rakeshkumar1890]

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

[JordyWegman]

Hi Rakesh,

You will need to create some indicators for this in order to work properly. Your script should look something like this:

// Create a key for joining data later
tmpTable:
Load
   OrderLine & '|' & max(LongestTime)   as %OrderLineMaxTime,
   OrderLine & '|' & Category           as %OrderLineCategory  
   *    
From {YourSource} (qvd):

// Create key for join and get the max time. Create the indicator for later
Left Join (tmpTable)
Load
   OrderLine & '|' & max(LongestTime)   as %OrderLineMaxTime
   max(LongestTime) as LongestTime
   1                as _indLongestTime     
From {YourSource} (qvd)
Group by OrderLine;

//  You also need to know which one has the highest count for the ones with the longest time.
Noconcatenate:
Load
   OrderLine & '|' & Category   as %OrderLineCategory   
   Max(OrderNo.)                as _cntOrderNo
   1                            as _indCountOrderNo
Resident tmpTable
Where _indLongestTime = 1
Group by OrderLine, Category;

// Then load the table and get the indicators:
Load
*
Resident tmpTable
Where _indCountOrderNo = 1 and _indLongestTime = 1;

 Jordy

Climber

[rakeshkumar1890]

Thanks  Wegman for your inputs.

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

[rakeshkumar1890]

@JordyWegman  : Your comment -- 

// You also need to know which one has the highest count for the ones with the longest time

It should be Highest count(OrderNo) for each OrderLine according the category preference. 

No need to Max(OrderNo) -- It may be any (Highest count).

[Taoufiq_Zarra]

https://community.qlik.com/t5/New-to-QlikView/Logical-Condition/td-p/1751652

@rakeshkumar1890  can you check

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:

[Taoufiq_Zarra · Accepted solution]

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;

[rakeshkumar1890]

Big thanks @Taoufiq_Zarra