topic Re: what is going wrong with my set expression ? in QlikView

what is going wrong with my set expression ?

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

Hello Sirs ,

For below data i wanted output like

customer QUALITY for year 2017 and 2018 , with minimum priority and Logistics as OV , HYBRID as null and class as A .

NEEDED RESULT

CUST	2017 - MIN PRI QUALITY	2018 - MIN PRI QUALITY
CUSTOMER 1	8	5
CUSTOMER 2	8	3
CUSTOMER 3	-	8

INPUT TABLE :

CUSTOMER	YEAR	PRIORITY	logistics	HYBRID	CLASS	QUALITY_ON_TEN
CUSTOMER 1	2018	6				9
CUSTOMER 1	2017	3		HY	A	1
CUSTOMER 1	2018	4	NU	LY		5
CUSTOMER 1	2017	4	NU	LY	A	2
CUSTOMER 1	2017	2	OV			7
CUSTOMER 1	2017	1	OV		A	8
CUSTOMER 1	2018	2	OV		A	5
CUSTOMER 1	2018	3	OV	HY	A	7
CUSTOMER 2	2017	4		HY		6
CUSTOMER 2	2017	6	NU	LY	A	1
CUSTOMER 2	2018	7	NU	LY	A	6
CUSTOMER 2	2018	1	OV			3
CUSTOMER 2	2017	3	OV			8
CUSTOMER 2	2017	0	OV		A	8
CUSTOMER 2	2018	1	OV		A	3
CUSTOMER 2	2018	3	OV	HY		6
CUSTOMER 2	2018	5	OV	HY	A	6
CUSTOMER 2	2017	2	OV	LY	A	1
CUSTOMER 3	2018	5			A	4
CUSTOMER 3	2018	6	NU			5
CUSTOMER 3	2018	2	NU		A	7
CUSTOMER 3	2018	-3	OV		A	8
CUSTOMER 3	2017	2	OV		A	8
CUSTOMER 3	2018	6	OV		A	0
CUSTOMER 3	2017	1			A	8

My expression which did not work

Concat(DISTINCT{<YEAR={2017}>*<logistics={'OV'}>*<HYBRID_L={0}>*<PRIORITY={"$(=FirstSortedValue(PRIORITY,aggr(min(PRIORITY),CUSTOMER,PRIORITY)))"}>}QUALITY_ON_TEN)

Concat(DISTINCT{<YEAR={2018}>*<logistics={'OV'}>*<HYBRID_L={0}>*<PRIORITY={"$(=FirstSortedValue(PRIORITY,aggr(min(PRIORITY),CUSTOMER,PRIORITY)))"}>}QUALITY_ON_TEN)

Re: what is going wrong with my set expression ?

prahlad_infy — Thu, 07 Mar 2019 16:17:31 GMT

scrit

cust:
LOAD * ,

Len(HYBRID) AS HYBRID_L ;

LOAD * INLINE [

CUSTOMER, YEAR, PRIORITY, logistics, HYBRID, CLASS, QUALITY_ON_TEN
CUSTOMER 1, 2018, 6, , , , 9
CUSTOMER 1, 2017, 3, , HY, A, 1
CUSTOMER 1, 2018, 4, NU, LY, , 5
CUSTOMER 1, 2017, 4, NU, LY, A, 2
CUSTOMER 1, 2017, 2, OV, , , 7
CUSTOMER 1, 2017, 1, OV, , A, 8
CUSTOMER 1, 2018, 2, OV, , A, 5
CUSTOMER 1, 2018, 3, OV, HY, A, 7
CUSTOMER 2, 2017, 4, , HY, , 6
CUSTOMER 2, 2017, 6, NU, LY, A, 1
CUSTOMER 2, 2018, 7, NU, LY, A, 6
CUSTOMER 2, 2018, 1, OV, , , 3
CUSTOMER 2, 2017, 3, OV, , , 8
CUSTOMER 2, 2017, 0, OV, , A, 8
CUSTOMER 2, 2018, 1, OV, , A, 3
CUSTOMER 2, 2018, 3, OV, HY, , 6
CUSTOMER 2, 2018, 5, OV, HY, A, 6
CUSTOMER 2, 2017, 2, OV, LY, A, 1
CUSTOMER 3, 2018, 5, , , A, 4
CUSTOMER 3, 2018, 6, NU, , , 5
CUSTOMER 3, 2018, 2, NU, , A, 7
CUSTOMER 3, 2018, -3, OV, , A, 8
CUSTOMER 3, 2017, 2, OV, , A, 8
CUSTOMER 3, 2018, 6, OV, , A, 0
CUSTOMER 3, 2017, 1, , , A, 8
];

Re: what is going wrong with my set expression ?

prahlad_infy — Thu, 07 Mar 2019 16:44:38 GMT

@pradosh_thakur ..

@anushree1 , @Anil_Babu_Samineni

Sir you all gave me solution for one previous doubt . hence adding you

Re: what is going wrong with my set expression ?

anushree1 — Fri, 08 Mar 2019 07:18:50 GMT

Hi Please find the attached , output on qv matches your desired output except for Customer 3 I am unsure if its typo error on customer 3 or the logic different, please check and let me know

Re: what is going wrong with my set expression ?

prahlad_infy — Fri, 08 Mar 2019 08:31:59 GMT

Thank You Anushree1 for the reply .

Actually for customer 3 for 2017 the lowest priority is 1 , but its logistics field is NULL , though CLASS is A ...
Hence , it should have Null result
while for 2018 the lowest priority is - 3 hence it should display 8 as result

our search cond should like be

1) first find the lowest priority
2) check if corresponding Logistics is OV , if yes then

3) Check class if its A , if yes then populate corresponding QUALITY_ON_TEN

else populating Null

Hope i am explaining myself clear .

Re: what is going wrong with my set expression ?

prahlad_infy — Fri, 08 Mar 2019 09:00:08 GMT

I made little change in data and your expression in not giving expected result ;

Final:
Load *,
if(len(trim(HYBRID))=0,1,0) as Null_Flag;

Temp:
LOAD * INLINE [

CUSTOMER, YEAR, PRIORITY, logistics, HYBRID, CLASS, QUALITY_ON_TEN
CUSTOMER 1, 2018, 6, , , , 9
CUSTOMER 1, 2017, 3, , HY, A, 1
CUSTOMER 1, 2018, 4, NU, LY, , 5
CUSTOMER 1, 2017, 4, NU, LY, A, 2
CUSTOMER 1, 2017, 2, OV, , , 7
CUSTOMER 1, 2017, 8, OV, , A, 8
CUSTOMER 1, 2017, 1, OV, , A, 10
CUSTOMER 1, 2018, 7, OV, , A, 5
CUSTOMER 1, 2018, 1, OV, , A, 9
CUSTOMER 1, 2018, 3, OV, HY, A, 7
CUSTOMER 2, 2017, 4, , HY, , 6
CUSTOMER 2, 2017, 6, NU, LY, A, 1
CUSTOMER 2, 2018, 7, NU, LY, A, 6
CUSTOMER 2, 2018, 1, OV, , , 3
CUSTOMER 2, 2017, 3, OV, , , 8
CUSTOMER 2, 2017, 0, OV, , A, 8
CUSTOMER 2, 2018, 1, OV, , A, 3
CUSTOMER 2, 2018, 3, OV, HY, , 6
CUSTOMER 2, 2018, 5, OV, HY, A, 6
CUSTOMER 2, 2017, 2, OV, LY, A, 1
CUSTOMER 3, 2018, 5, , , A, 4
CUSTOMER 3, 2018, 6, NU, , , 5
CUSTOMER 3, 2018, 2, NU, , A, 7
CUSTOMER 3, 2018, -3, OV, , A, 8
CUSTOMER 3, 2017, 2, OV, , A, 8
CUSTOMER 3, 2018, 6, OV, , A, 0
CUSTOMER 3, 2017, 1, , , A, 8
];

10 should be the result for 2017 , 9 for 2018 .