Only script function not working for the below condition - In script

[Sud_qlik]

Hello Everyone,

I have a data like below,

Application ID	Application Name	Application State	Product ID	Product Name	Manufacturing Date	Product Status	Product Category
1	Application 1	EOL	P1	Product 1	12-May-18	100	SOFTWARE
1	Application 1	Custom	P2	Product 2	2-Sep-20	200	SOFTWARE
1	Application 1	Assessed	P3	Product 3	21-Jun-21	300	HARDWARE
2	Application 2	Extended	P4	Product 4	4-Jun-21	300	SOFTWARE
2	Application 2	Custom	P1	Product 1	12-May-18	300	SOFTWARE
2	Application 2	Extended	P1	Product 1	12-Aug-19	200	SOFTWARE
2	Application 2	Assessed	P6	Product 6	15-Jun-21	300	SOFTWARE
2	Application 2	Extended	P3	Product 3	21-Jun-21	300	HARDWARE
3	Application 3	Assessed	P7	Product 7	18-Jun-21	300	HARDWARE
3	Application 3	Extended	P8	Product 8	1-Jun-21	300	HARDWARE

I would like to identify the unique Application which has only this month's HARDWARE records with Status 300 like the following condition (Product Category = 'HARDWARE' and Product Status = '300' and (Application State <> 'Custom' , 'ÉOL') and Month & Year (Manufacturing Date) = Today's Month and Year (Let's consider June 2021)

Output should be,

Application Name

Application 1

Application 3

Reason:

Application 1 has only one record which is with HARDWARE category, 300 status, Assessed state and current month & year record - Condition satisfied

Application 2 has more than 1 record with 300 status, state and current month but not all are HARDWARE - Condition not satisfied

Application 3 has more than 1 record with 300 status, state and both are current month & year and also both falls under HARDWARE category - Condition Satisfied

I tried with Only() script function and it's not working.

Kindly help me with a query for this. I would like to use this in script not as set analysis.

@robert_mika @hic @johnw @sunny_talwar 

[eddie_wagt]

Hello,

If I manually select the rows on which your conditions apply, I get the these rows as expected output, right?

If so, the you can accomplish this with the following Set Analysis expression.

count({$<[Product Category]={'HARDWARE'},[Product Status]={300},[Application State]-={'Custom' , 'ÉOL'},YearMonth={"=year(today())&num(month(today()),'00')"}>}[Application ID])

I transformed the manufacturing date to YearMonth in the script, normally the date should be connected with a Calendar. And I would normally put the expression "=year(today())&num(month(today()),'00')" in a variable or I would set a flag in the calendar.

LOAD year([CalcDate])&num(month([CalcDate]),'00') as YearMonth
, *
;
LOAD date(date#([Manufacturing Date],'dd-MMM-yy')) as CalcDate
, *
;
LOAD * INLINE [Application ID Application Name Application State Product ID Product Name Manufacturing Date Product Status Product Category
1 Application 1 EOL P1 Product 1 12-May-18 100 SOFTWARE
1 Application 1 Custom P2 Product 2 2-Sep-20 200 SOFTWARE
1 Application 1 Assessed P3 Product 3 21-Jun-21 300 HARDWARE
2 Application 2 Extended P4 Product 4 4-Jun-21 300 SOFTWARE
2 Application 2 Custom P1 Product 1 12-May-18 300 SOFTWARE
2 Application 2 Extended P1 Product 1 12-Aug-19 200 SOFTWARE
2 Application 2 Assessed P6 Product 6 15-Jun-21 300 SOFTWARE
2 Application 2 Custom P3 Product 3 21-Jun-21 300 HARDWARE
3 Application 3 Assessed P7 Product 7 18-Jun-21 300 HARDWARE
3 Application 3 Extended P8 Product 8 1-Jun-21 300 HARDWARE
] (delimiter is ' ')
;
 

Somewhat I get the feeling you are looking for something else regarding to the three apps you are referring to. I think it is wise to check if all fields have the same data format. Or maybe you are looking for something else. Let me/us know what you are exactly are looking for.

Regards Eddie

[Sud_qlik]

@eddie_wagt  Thanks for your response. Our intention is to get only the application which fails only with 'HARDWARE'. If we have a mix of SOFTWARE and HARDWARE with all the conditions fulfilled then those applications shouldn't come in the result.

Application 2 should not come in the result as P4, P6 and P3 fulfil all conditions but it has a combination of SOFTWARE and HARDWARE.

But, Application 1 has only one record which fulfil all condition with HARDWARE category and Application 3 has two records which fulfil all conditions with both HARDWARE category.

Also, I need this to be in the script not in set analysis.

Hope this is clear.

[Sud_qlik]

I get the desired result with the following set analysis query but I would like to bring this in script. In script when I use only function with group by it is not giving desired result.

=if(not isnull(AGGR(only({$<[Product Category]={'HARDWARE'},
[Product Status]={'300'},
[Calendar Month / Year]={"$(=date(today(),'MMM YYYY'))"}>
-
<[Product Category]={'SOFTWARE'},
[Product Status]={'300'},
[Calendar Month / Year]={"$(=date(today(),'MMM YYYY'))"}>
-
<[Application State]={'End of Life','Custom'}>
} [APPLICATION ID]),[APPLICATION ID])),'HW Only EOL')

[Sud_qlik]

Guys, Please help me fix this issue ASAP.

[sunny_talwar]

So, if you are doing this in the script, you expect to see 5 rows returned (3 for Application 1 and 2 for Application 3)?